How to get running total for previous period based on hierarchary

[parry2k]

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

[Sean · Accepted 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!

[parry2k]

I'm going to try right now and let you know. Thanks!

[parry2k]

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? 

[Sean]

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)

[parry2k]

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