Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi there,
I have calendar table with hierarchary that has year/quarter/month:
I have a measure to calculate running total:
Activated = CALCULATE(SUM(Locations[Activated Flag]), FILTER(ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date])))
So whatever level I go in hierarchary I get the running total (year, quarter or month)
Now I want to compare this running total with previous (Year, Quarter or Month) depends on what level I'm on the hierarchary and that is where I need help.
Thanks,
P
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Solved! Go to Solution.
If this is not exactly what you want you can adjust it to fit your specific requirement!
But basically you need to change the calculation (as you require) depending on what level of the Hierarchy you are on.
Measure = IF ( ISFILTERED ( 'Calendar'[Date].[Month] ), CALCULATE ( [Running Total Measure], FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date].[MonthNo] <= MAX ( 'Calendar'[Date].[MonthNo] ) - 1 ) ), IF ( ISFILTERED ( 'Calendar'[Date].[Quarter] ), CALCULATE ( [Running Total Measure], FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date].[QuarterNo] <= MAX ( 'Calendar'[Date].[QuarterNo] ) - 1 ) ), CALCULATE ( [Running Total Measure], FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date].[Year] <= MAX ( 'Calendar'[Date].[Year] ) - 1 ) ) ) )
Hope this helps!
If this is not exactly what you want you can adjust it to fit your specific requirement!
But basically you need to change the calculation (as you require) depending on what level of the Hierarchy you are on.
Measure = IF ( ISFILTERED ( 'Calendar'[Date].[Month] ), CALCULATE ( [Running Total Measure], FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date].[MonthNo] <= MAX ( 'Calendar'[Date].[MonthNo] ) - 1 ) ), IF ( ISFILTERED ( 'Calendar'[Date].[Quarter] ), CALCULATE ( [Running Total Measure], FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date].[QuarterNo] <= MAX ( 'Calendar'[Date].[QuarterNo] ) - 1 ) ), CALCULATE ( [Running Total Measure], FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date].[Year] <= MAX ( 'Calendar'[Date].[Year] ) - 1 ) ) ) )
Hope this helps!
I'm going to try right now and let you know. Thanks!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Quick question, you used Runnign Total Measure in your formula, do I have to precalculate that like this:
Running Total Measure = CALCULATE(SUM(Locations[Activated Flag]), FILTER(ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date])))
Also I created custom hierarchary, not using Date field for hierarchary. Will this still work?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Well obviously you will have to adjust the formula for your Custom Hierarchy!
But Don't change the order of IFs!!!
Start testing with the innermost level of the Hierarchy and go a step at a time to the outer levels!
However I know this pattern works!
The Definitive Guide to DAX (p342-343)
Here is what i did but still need to work on IF condition:
I created different measures based on following:
- Added following two new columns in calendar table
Previous Quarter Last Date = DATEADD(STARTOFQUARTER('Calendar'[Date]), -1, DAY)
Previous Year Last Date = DATEADD(STARTOFYEAR('Calendar'[Date]), -1, DAY)
Added two new measures for running total using above dates to get total until previous quarter and previous year using above two columns, so if throw all this in table, it looks good.
Now matter of how to use above IF condition to find out the hiearchary. Just to let you know I have YEAR as a slicer as well and I think that is having impact on IF condition formulat you suggest. Thanks for your help.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
98 | |
39 | |
30 |