cancel
Showing results for
Did you mean:
Frequent Visitor

## Cumulative Totals with date slicer looking back N Months

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

1 ACCEPTED SOLUTION
Super User

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] )
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

3 REPLIES 3
Super User

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] )
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

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

🙂

Frequent Visitor

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

Thanks 🙂

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!