Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous 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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

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

@Anonymous 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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

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

Anonymous
Not applicable

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 👍

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.