Power BI How To: Adding a Monthly/Annual Toggle to a Dashboard

Power BI How To: Adding a Monthly/Annual switch to a Dashboard

In a scenario where you are predicting sales or costs in Power BI, you cannot quickly switch between monthly and yearly estimates. To address this issue, a series of Power BI measures would have to be created in conjunction with bookmarks. This solution can often become messy and confusing. Having the ability to switch between monthly and yearly predictions in one click saves valuable time and effort. This tutorial will show you how to create a “Monthly/Yearly” toggle that can be utilized on a large variety of dashboards.

**For context, the data below is a projection for the cost of migrating specific applications from on-prem servers to Azure. The costs include compute costs and storage costs for the user. The end user wanted to be able to toggle between monthly and yearly projections with one click, which required the data in each column to be dynamic and change as the “Monthly/Yearly” toggle was selected.

This is an example of the original dashboard that displays monthly cost projections for each environment within a certain application. There is obvious value in being able to see yearly costs in one click, so how do we do that!?

Adding the Monthly/Yearly Slicer

Adding a slicer to toggle between monthly and yearly projections can be done very quickly. The first step is to create a new table, that will be what is called a “disconnected table”, that serves as the switch for the data displayed. This can be done easily by clicking the Enter Data function on the Home tab.

The data in the table is the values need for the “Monthly/Yearly” switch. Name this table Period_Table. You will need just two columns, a Period_ID column and a Period_Duration column. These two columns will contain your monthly and yearly values and could even contain daily and weekly iterations if you so choose.

This table, as mentioned above, is what is called a “disconnected table”. This means that the Period_Table that we just created has no link to any other tables in your dataset. Thus, you do not have to add any relationships to this table to make the toggle switch work correctly.

The next step is adding the “Monthly/Yearly” slicer. To add a slicer, navigate to the Power BI Visualizations pane and select the slicer icon.

Next, drag the Period_Duration column into the category property of the slicer.

After doing some stylistic detail and making the new slicer match our current theme, we now have a “Monthly/Yearly” slicer that will convert our data with one click. But giving it a test click yields no response! We need to create a measure that reacts to our selection on the “Monthly/Yearly” toggle!

To do this, create a new measure and name it Period_Selection. Enter the following formula, which utilizes the MIN function.

              Period_Selection = MIN(‘Period_Table’[Period_ID])

 The last step to do before the “Monthly/Yearly” toggle works is to link the new Period_Selection measure to your data. For example, we display the total monthly compute costs in the table located on the bottom left of the dashboard. These values are obtained through a measure called Total Compute Costs, which is simply a sum of a column of one of the tables.

              Total Compute Costs = SUM(‘Assessment_Summary$'[ComputeMonthlyCost])

In order to have our Total Compute Costs measure compute yearly values when the toggle is selected, we will use the switch function and add to the existing Total Compute Costs measure to make it able to compute both monthly and yearly costs. The SWITCH() function returns different results based on an expression. If the value is 1 (Monthly), the expression returns monthly cost values. If the value is 2, it calculated the projected costs annually. An example of the formula and correct syntax is shown below.

Now the “Monthly/Yearly” toggle will work and correctly change the data based on your slicer! You must go and add the SWITCH() function to all measures that you want changed when the “Monthly/Yearly” toggle is selected. Other uses for this toggle include a “Monthly/YTD” slicer and a “Rolling Four Week Total / Monthly”. I hope this tutorial helps and stay tuned for more Power BI How To’s!