Cumulative Totals with date slicer looking back N Months

[Walden2002]

I need help in turning a measure which calculates monthly totals into one that calculates cumulative totals each month.

I have a dashboard with a Period Reporting Date slicer which fitlers all the data on the page for that particular reporting period.

So far nice and easy. However one analysis I need to present is monthly cumulative totals up to and including that month.  

If have a measure which can calculate individual monthtly totals and allows users to select how many previous months to display

SUM Waste Last N Months =

CALCULATE(

SUM('total_all'[Value]),

DATESINPERIOD ( 'Reporting Periods'[Reporting Period Date] , MAX ( 'Reporting Periods'[Reporting Period Date] ),-[N Value], MONTH ))

So if February 22 is selected on the slicer and N is set to 5 then I can get a table like this

But what I'd really like is a measure to enable me to have the table above but with cumulative values for each month.

All help appreciated

Thanks

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I suggest having a disconnected slicer table like the attached.

Last N months cumulate measure: =
VAR selectedperiod =
    MAX ( 'Slicer table'[End of Month] )
VAR N_month = 'N Month'[N Month Value]
RETURN
    IF (
        MIN ( 'Calendar'[Date] ) <= selectedperiod,
        CALCULATE (
            SUM ( Data[Quantity] ),
            DATESINPERIOD ( 'Calendar'[Date], selectedperiod, - N_month, MONTH ),
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        )
    )

[Walden2002]

Yes - thank you very much - apologies for the late reply I was on leave 

🙂

[Walden2002]

great - I'll have a look through and get back to you

Thanks 🙂