Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Walden2002
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
 
Walden2002_0-1653034405287.png

 

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
Jihwan_Kim
Super User
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.

 

Untitled.png

 

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.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
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.

 

Untitled.png

 

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.


Go to My LinkedIn Page


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

🙂

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

 

Thanks 🙂

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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