cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Super User

## How to get running total for previous period based on hierarchary

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.

1 ACCEPTED SOLUTION
Community Champion

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!

5 REPLIES 5
Community Champion

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!

Super User

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.

Super User

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.

Community Champion

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)

Super User

@Sean

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.