Last 12 months total with multiple groupings

[Anonymous]

Image 2Image 2Image 1

Hi all, 

I am fairly new to Power BI and wanted to build a last 12 months running total calculation. To give an idea of what I trying to achieve, I've attached a sample data.

1 RU with 1 P&L item (COGS) will have 36 rows i.e 3 years data (2019 -12 months, 2020-12 months, 2021-12 months) at a aggregated level, with sum of amount. I want to calculate the running last 12 months total for any month starting from 2019-12 and then all the way upto 2021-12. (Refer to 1001-COGS image for reference)

When my combination changes from 1001 COGS to 1001 Gross Sales, again the last 12 months rolling sum logic will reset and it will start from 2019-12 and go to 2021-12 (refer 1001- Gross Sales image for the same).

Any help regarding this would be highly appreciated, have been stuck on this since a while. TIA.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a calculated column called 'YearMonth'.

YearMonth = [Year]*100+[Period]

Then you may try the following calculated column or measure to get the result.

Calculated column:

Result Column = 
IF(
    [YearMonth]>=201912,
    var ym = [YearMonth]
    var tab = 
    TOPN(
        12,
        CALCULATETABLE(
            DISTINCT('Table'[YearMonth]),
            FILTER(
                ALL('Table'),
                [RU]=EARLIER('Table'[RU])&&
                [P&L Line]=EARLIER('Table'[P&L Line])&&
                [YearMonth]<=ym
            )
        ),
        [YearMonth]
    )
    return
    CALCULATE(
        SUM('Table'[Amount]),
        FILTER(
            ALLEXCEPT('Table','Table'[RU],'Table'[P&L Line]),
            [YearMonth] in tab
        )
    )
)

Measure:

Result Measure = 
IF(
    MAX('Table'[YearMonth])>=201912,
    var ym = MAX('Table'[YearMonth])
    var tab = 
    TOPN(
        12,
        CALCULATETABLE(
            DISTINCT('Table'[YearMonth]),
            FILTER(
                ALL('Table'),
                [RU]=MAX('Table'[RU])&&
                [P&L Line]=MAX('Table'[P&L Line])&&
                [YearMonth]<=ym
            )
        ),
        [YearMonth]
    )
    return
    CALCULATE(
        SUM('Table'[Amount]),
        FILTER(
            ALLEXCEPT('Table','Table'[RU],'Table'[P&L Line]),
            [YearMonth] in tab
        )
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks a lot @v-alq-msft this solution worked perfectly. Awesome!

[amitchandak]

@Anonymous , if you have date or you can create a date like

date = date([year], [period],1)

With help from date table , you can get 12 month rolling

Rolling 12 = CALCULATE(sum(Table[Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

You can add filter of COGS and Gross Sales

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Thanks @amitchandak for the prompt reply. I did try this but it is still not working, ideally there should be no value from 2019-01 to 2019-11, the first total should come at 2019-12 for a given combination of RU & P&L Item. Also in the given example image, my data is already at an aggregated level so cannot use sum in the calculate syntax.