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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
parry2k
Super User
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
Sean
Community Champion
Community Champion

If this is not exactly what you want you can adjust it to fit your specific requirement! Smiley Happy

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! Smiley Happy

 

View solution in original post

5 REPLIES 5
Sean
Community Champion
Community Champion

If this is not exactly what you want you can adjust it to fit your specific requirement! Smiley Happy

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! Smiley Happy

 

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.

Sean
Community Champion
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! Smiley Happy

The Definitive Guide to DAX (p342-343)

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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