Time Intelligence in Power BI with Buttons

[Anonymous]

Hi,

I am wondering how to create three buttons (1mo vs 1mo, 3mo vs 3mo, and 12mo vs 12mo) so the user can make a selection and see the visuals update across the entire report.

I am having a tough time understanding how to make this work. I am currently using a relative date filter to display information in the month of June. Then I am using the date add function to compare June to the month of May.

The goal is for the user to have the ability to display the most recently completed one-month periods (June vs. May), three-month periods (June, May, April (current period) vs. March, January, and February (previous period), and 12mo periods to each other (June, May, April, March, February, January, December, November, October, September, August, July (current period) vs. (June, May, April, March, February, January, December, November, October, September, August, July (previous period) by making a selection from one of the three buttons at the top of the report.

I am finding the comparison relatively simple to do when the visuals are only showing one period, such as a 1-month comparison (June vs. May), but where I am becoming confused is how the visuals can be updated to a 3mo comparison or a 12mo comparison by selecting a button for that period, instead of having to change out the measure manually every time a new time comparison is requested.

The reason why I am saying 1mo period, 3mo period, and 12mo period instead of month, quarter, and year is because the dates are rolling, so the current period will always be the most recently completed month, three months or 12 months. I am using the relative date filter to achieve this at the moment where I specify "Show items when the value is in the last 1 calendar month" (for month vs month), "3 calendar months" (for 3mo vs. 3mo), and "12 calendar months" for (12mo vs. 12mo). When I use this method, I must change the filter and measure when I would like to go from 1mo vs. 1mo to 3mo vs. 3mo, to 12mo vs. 12mo. This way does work, and it will only show a comparison between the most recently completed month (June vs May), 3 months (June, May, April (current period) vs. March, January, and February (previous period), or 12 months, but I am wondering how the visuals can be updated automatically to display the same information, but by selecting a button at the top of the report instead of having to change the measure each time I would like to make a different time comparison.

TIA!

[Anonymous · Accepted solution]

Hi Everyone,

Here is the solution that I have found to work. I hope someone finds this useful. I will give credit to the freelancer who came up with this at the end of the post.

End Result:

You will have one slicer for the current period and one slicer for the previous period. You can compare any range of dates to one another by selecting your date range in the corresponding slicer. In the photo below the current period slicer is showing 6/1/2021-6/30/2021 and the previous period slicer is showing 5/1/2021-5/31/2021. The light blue line is showing the current period and the dark blue line is showing the previous period in the visuals.

Step 1:

Create a new measure called "Previous Date Selector" and use your date table as the parameter value.

Step 2:

Create an inactive one too many relationship between your "Previous Date Selector" and regular date table.

Step 3:

Create a slicer from your standard date table and name it "current period" and create a slicer from your "previous date selector" and name it "previous period."

Step 4:

Create a measure with the following dax. In this case, I am comparing total sessions in the current period to total sessions in the previous period so I am using the "total sessions" value.

Step 5

Add your two values to the visual you would like to use to compare the current period to the previous period. In this case, I am comparing total sessions from google analytics so I have a measure for "total sessions", which is synched to the current period slicer and a measure for "total visitors for previous period", which is synched to the previous period slicer.

I hope this helps someone! 

Freelancer: andystepas | Profile | Fiverr

[mahoneypat]

The simplest way to do that (assuming your have your 3 DAX expressions to get 1, 3, and 12 mo comparisons) would be to make a calculation group.  You could then put the calc group in a slicer so the user could choose the time frame to compare.

If you really need buttons, you can do that with bookmarks and make 3 versions of the visual that overlap and set up the bookmarks with only one version showing at a time.

Pat

[Anonymous]

Hi @mahoneypat 

thank you for the response.

If you think the solution that is posted above is useful please consider accepting it as a solution.

Thank you,

Joshua Cortigiano