cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Animanga008
Helper II
Helper II

How to show Last Month values based on Slicer/Filter Action?

I am trying to create two tables on my dashboard. One that shows the fields selected by the current slicer. Another that shows the last month. How do I create the second table so that, whenever I choose a date, it will automatically populate with the last month's values?

 

See below for example:

 

Example.png

 

One method I considered is to just create a new column that shifts the month off by one. But not sure if that's the most elegant solution.

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@Animanga008 Are your values only monthly?

 

You could try creating a measure that counts the books and add that to the table, then create another one for count previous month and add that to the other table:

 

Count Books = COUNT( Table[Book Title] ) 

PM Count Books = CALCULATE( [Count Books], DATEADD(Date[Date] , -1, Month) )

 

You'll need a Date table for this, and would be ideal to have a Book Title table too. https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

3 REPLIES 3
AllisonKennedy
Super User
Super User

@Animanga008 Are your values only monthly?

 

You could try creating a measure that counts the books and add that to the table, then create another one for count previous month and add that to the other table:

 

Count Books = COUNT( Table[Book Title] ) 

PM Count Books = CALCULATE( [Count Books], DATEADD(Date[Date] , -1, Month) )

 

You'll need a Date table for this, and would be ideal to have a Book Title table too. https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

I think that's def a solution, creating two unrelated tables with the logic baked in. IHowever, I'm curious if there is some magical DAX formula you can use in absence of creating more tables (outside of a Date table). I suspect there isn't, but curious to see what the community says. But your suggestion would work. It's similar in concept to the YT link I posted below 👍

Animanga008
Helper II
Helper II

One solution would be to follow this YT video: https://www.youtube.com/watch?v=duMSovyosXE

 

Essentially, you build an unrelated data table that you build a measure off of. However, this method does not work if you want one singular date slicer to filter BOTH a current selection table AND a last month table.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors