cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joshcorti11
Helper III
Helper III

Time Intelligence in Power BI with Buttons

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!

1 ACCEPTED SOLUTION
joshcorti11
Helper III
Helper III

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.

joshcorti11_0-1626645675826.png

 

 

 

 

Step 1:

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

joshcorti11_1-1626645675575.png

 

 

Step 2:

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

joshcorti11_2-1626645675524.png

 

 

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."

 

joshcorti11_3-1626645675605.png

 

 

joshcorti11_4-1626645675801.png

 

 

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.

 

joshcorti11_5-1626645675588.png

 

 

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.

joshcorti11_6-1626645675812.png

 

 

I hope this helps someone! 

 

Freelancer: andystepas | Profile | Fiverr

View solution in original post

3 REPLIES 3
joshcorti11
Helper III
Helper III

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.

joshcorti11_0-1626645675826.png

 

 

 

 

Step 1:

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

joshcorti11_1-1626645675575.png

 

 

Step 2:

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

joshcorti11_2-1626645675524.png

 

 

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."

 

joshcorti11_3-1626645675605.png

 

 

joshcorti11_4-1626645675801.png

 

 

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.

 

joshcorti11_5-1626645675588.png

 

 

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.

joshcorti11_6-1626645675812.png

 

 

I hope this helps someone! 

 

Freelancer: andystepas | Profile | Fiverr

View solution in original post

mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!