Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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.
Solved! Go to Solution.
@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
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 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
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
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 👍
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |