Posts

Burning Questions: How to Add a Monthly/YTD Switch to a Power BI Dashboard?

One of the questions we get most often by our users is how to create a switch in their Power BI dashboard that would let them switch between Monthly and YTD views. You can do this by using Data Analysis Expressions (DAX), which is the formula language used throughout Power BI. This blog post will provide a quick overview of what you need to do to create a simple switch.

Before you start, download the examples from our webinar page for Top 5 DAX tricks for super effective Power BI dashboards. We also recommend that you watch the video as it also contains detailed step-by-step instructions for preparing your data and tables that are not covered in this post.

Start by opening the Sales Dashboard example (SalesDashboard-ZebraBI-webinar.pbix) from the zip file you downloaded. Open a new page by clicking on the + icon on the bottom of the start page.

The example already includes all the tables, relationships between them and code, so you just need to recreate the basic sales dashboard you will add the Monthly/YTD switch to.

Recreating the basic visualization

Start by recreating the main visual in your report. Add a Zebra BI Power Chart and select the following fields: Group under Business Units, Month under Calendar, AC and Revenue PY under Sales. Set them to the corresponding Fields as shown on the image below:

Now combine various visuals. For example, you can add a comparison to previous year using another dimension. Add a Power Table and select the fields as shown:

This table adds a comparison of actual results to the revenues from the previous year by individual product groups and divisions.

This visual is fully responsive and also controls the small multiples visual on the right that you've created in the previous step. If you click on a category, the chart on the right will change to display just the selected category or group of categories (division). With this, you've already created a great visual. However, we can make it better.

Adding slicers to your dashboard

Next stop is adding two slicers: Year and Month that you can use to switch between views for individual months and years.

Click on Year field in the Calendar group and drag and drop it onto the dashboard. Switch it to the Slicer visualization and select Dropdown option from the menu.

Add another slicer for months. Click on Month field in the Calendar group and drag and drop it onto the dashboard. Switch it to the Slicer visualization and again select the Dropdown menu item.

This will break your visualization. To fix it, you need to decouple the time series view from your slicer. Click on the month slicer and select the Format tab on your ribbon. Select the Edit interactions tool.

This shows additional controls on the dashboard, where you can control what impact the selected visualization should have on the others. To prevent the currently selected visualization element from impacting your small multiples chart, you should select the no impact icon.

Adding the Monthly/YTD switch to your dashboard

Now, we will add the Monthly/YTD Switch. Switch to Data view and add another table for the switch on the  dashboard. Go to the Relationship view, where you will create what is called a "disconnected table" and serves as a switch between two values. Click the Enter Data button in the Home tab and name it Period Calculation in the Name field on the next screen.

You need to create just two columns - Period calculation and CalcID. 

You now have a new table in your project that is available for you to select in Report view in Power BI. Drag and drop the Period Calculation field onto the chart and switch it to a slicer element. You get two options - monthly and YTD. Switch to Horizontal orientation under General option in the Formatting tab.

You now have a slicer with two options (YTD and Month). You click between the two although the options do not do anything yet.

Create a new measure and name it Selected calculation. You will use the MIN function, which simply returns the smallest value in a column. Enter the following formula: SelectedCalc = MIN('Period Calculation'[CalcID])

All you have to do now is to link this back to your model. You need to take your AC measure and use the following DAX formula:

AC = SWITCH([SelectedCalc];
1; [AC filtered];
2; CALCULATE([AC filtered]; DATESYTD('Calendar'[Date])))

Just to explain briefly, what these functions do. SWITCH returns different results depending on the value of an expression, in this case AC filtered element in your project. If the value is 1, the expression returns the AC filtered value. If the value is 2 it calculates revenue to return the YTD value for which the DATESYTD function is used.

Make sure you download the free trial!

Try Zebra BI Visuals for Power BI

In addition to out-of-the-box support for IBCS standards, Zebra BI visuals for Power BI feature 1-click data sorting, powerful outlier handling, advanced small multiples, responsive visuals, improved navigation, and full customization. With support for Power BI, best practice reporting is now available on the desktop and mobile and in the cloud.

 

Top 5 New Productivity Features in Zebra BI Custom Visuals for Power BI

The latest version of Zebra BI for Power BI brings a bunch of new features that bring new flexibility to companies and users who rely on standardized reports to present their data.


Reorder your columns and scale your reports

As you work with Zebra BI Power Tables you might want to change the default order of columns. While the default ordering is based on best practices and standards prescribed by IBCS, companies very often have reporting standards which define a proprietary order of columns or simply need more flexibility.

For example, data for the previous year should be displayed to the left of Actual results or to the right. With the latest update, you no longer have to worry, just simply drag and drop columns to reorder them.

The reordering is very flexible and intuitive. Your custom column order is preserved even after you resize your table. Some detail, particularly charts, might get converted into numbers to save space and retain readability, however, the information itself is not removed and stays front and center. This feature is great for scaling your stories from beautiful, highly visual full-screen power bi dashboards to extremely compact, mobile-friendly reports and everything in between ... with a simple resize action

Use this feature to add a new level of flexibility to your IBCS-compliant reports and dashboards. Our visuals now support virtually any customization you might need to create powerful and convincing data stories.

Focus on the most important things

To make a point, sometimes you just want to show the most significant items and reduce the visual clutter. To help you do just that, we've added the Top/bottom N feature to our waterfall charts. Once you toggle the option On, you simply select the number of top or bottom values you want to display and the chart adjusts automatically.

 

How to use this option? Once you have your waterfall chart, click the Format tab in the Visualizations pane. Open the Categories group and toggle the Top/Bottom N switch into the On position. Afterwards, you can set the number of items that need to be shown. Other items are merged into a single group named Other.

This feature helps you focus on the most important data points while reducing visual density to improve chart readability.

Automated data labelling

Everyone hates it when the labels on their charts overlap and ruin an otherwise perfect visual. We've added an advanced algorithm to Zebra BI PowerCharts to prevent this. When you are working with a narrow chart that cannot accommodate all the labels, the visual will hide them automatically. Labels appear again once you resize the chart or switch to the Focus mode. This ensures optimal label density depending on your context and situation.

To turn this feature on, select your chart and click the Format tab in the Visualizations pane. Open the Data labels group and select Auto from the Density dropdown menu.

Small multiples that combine chart types

Small multiples mode now supports charts combining multiple chart types (bar and lollipop, for example). To use this feature, select your chart and click the Format tab in the Visualizations pane. Open the Chart Settings group and select either "Absolute / Relative", "Actual / Absolute" or "Actual / Relative" from the Layout dropdown menu.

At the same time, we've added the Top N + Others option to the small multiple mode. It works similar to the feature described above by showing only the most important charts and combining the others into a single chart named Other.

To use this feature, select your chart and click the Format tab in the Visualizations pane. Open the Small multiples group and select an item from the Top N dropdown menu. Then, set the number of items you want to have shown.

Break the axis in line and area charts

Sometimes you want to track a difference in a KPI that has very small variations, you can now break the axis in line and area charts to emphasize otherwise minor variances. The feature works on individual charts as well as small multiples.

Our advice is to be careful with this feature, because it artificially emphasizes differences where there might not be any. However, you might find this feature useful if you want to focus on the trend of a variance or a similar feature of your data.

You can find the axis break option in the Format tab in the Visualizations pane under the Axis break group. Turn the feature on with the toggle and set the Percent value - lower values might result in a better display of very low data labels.

Make sure you download the free trial!

In addition to out-of-the-box support for IBCS standards, Zebra BI visuals for Power BI feature 1-click data sorting, powerful outlier handling, advanced small multiples, responsive visuals, improved navigation, and full customization. With support for Power BI, best practice reporting is now available on the desktop and mobile and in the cloud.

Try Zebra BI Visuals for Power BI

Struggling to visualize your data? Zebra BI’s got you covered!

Whether it’s waterfall charts, variance charts, or lollipop charts, Zebra BI has everything you need to visualize your data for all to understand.

Picture this: you’re about to present your company’s quarterly earnings to its top executives. You’ve spent countless hours gathering the needed information and placing it into a chart in Excel. You’ve even tried your hardest to make the chart look as appealing as possible. (Unfortunately, you thought this entailed simply adding different colors to different columns.) Upon beginning your presentation, however, you notice that your report looks similar to the one shown below and might be difficult for people not familiar with the information to properly analyze or understand.

Read more