Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors