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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JMSNYC
Helper III
Helper III

Rolling Sum of a Cumulative Total

Hello again.

 

Still cannot figure out the DAX to solve my issue:

 

First column is yearmonth

 

Second column is monthly sales

 

Third column is cumulative sales since the launch of the product (e.g., 659 = 655+4), calculated via the following Measure:

Cumulative_Sales = CALCULATE('FACT'[Sales],FILTER(ALLSELECTED('FACT'),'Calendar'[Date]<=max('Calendar'[Date])))
 

Fourth column is rolling 3 month sum of Second column (e.g. 64 = 28+4+32), calculated via the following Measure

3M_Rolling_Sales =
CALCULATE (sum('FACT'[Sales]),DATESINPERIOD ('Calendar'[Date],LASTDATE ( 'Calendar'[Date] ),-3,MONTH))
 
Fifth column ... WHAT IS THE MEASURE'S EXPRESSION for the 3M_Rolling_CUMULATIVE_Sales? I cannot generate it
3M_Rolling_CUMULATIVE_Sales ... i.e. for 201905 ... I should have: 687+659+655 = 2,001

e7fa3d79a00a6a76b284ff12093836c9.png

 

Could anyone please help ?

Thank you in advance.

1 ACCEPTED SOLUTION

@JMSNYC 

 

Sorry, I'm not sure what you mean. I believe the measure creates a cumulative sum of your rolling 3 month sales.

"Fifth column ... WHAT IS THE MEASURE'S EXPRESSION for the 3M_Rolling_CUMULATIVE_Sales?"

Isn't that what you need?

cumulsales.JPG

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

6 REPLIES 6
PaulDBrown
Community Champion
Community Champion

@JMSNYC 

Try:

 

3 Month Cumulative =
SUMX (
    SUMMARIZE (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        ),
        'Calendar'[YearMonth],
        "3MonthRSales", [3M_Rolling_Sales]
    ),
    [3MonthRSales]
)

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Getting close: This created the cumulative sum of the total column since product launch. How limit that to a 3 month rolling sum ?

@JMSNYC 

 

Sorry, I'm not sure what you mean. I believe the measure creates a cumulative sum of your rolling 3 month sales.

"Fifth column ... WHAT IS THE MEASURE'S EXPRESSION for the 3M_Rolling_CUMULATIVE_Sales?"

Isn't that what you need?

cumulsales.JPG

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Awesome ! Actually it worked great. Previous issue was myy mistake. Was taking the wrong variable !!

amitchandak
Super User
Super User

@JMSNYC , Create a measure like this with date table and try

 

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

Unfortunately, it does not work. This is the formula I used in Column 4 (with LASTDATE vs. MAX), but still gives me same output 😞

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.