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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Mjdrejza1722
Helper I
Helper I

Rolling Total / Cumulative Sum by month and by year

Thank you in advance for the help (including redirects to existing forum solutions).   I know this is "old hat," but how do I get the trends to span multiple years instead of alternating years each month.  If I have a single year, the trends are fine, but when I select multiple years, the trends do this (below)... instead of trending up to December and then going back down to zero at the subsequent January.

Mjdrejza1722_1-1690312347714.png

  Mjdrejza1722_2-1690312370395.png



OnlySpend Cumulative =
CALCULATE(
    'Final Spend'[Spend],
    FILTER(
        ALLSELECTED('Calendar Table 2')
        ,'Calendar Table 2'[DATE] <=max('Calendar Table 2'[DATE]) ) )



1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Mjdrejza1722 , You need cumulative or YTD

 

for YTD, you ned have month on axis and year on legend in this case

 

with help from date table

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

 

Cumulative

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

 

 

Consider windows for every thing

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

 

https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Mjdrejza1722 , You need cumulative or YTD

 

for YTD, you ned have month on axis and year on legend in this case

 

with help from date table

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

 

Cumulative

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

 

 

Consider windows for every thing

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

 

https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

Thank you.  I eventually got it to work.   Final code was:

CALCULATE(SUMX('Final Spend', 'Final Spend'[Amount USD]),
    FILTER(
        ALLSELECTED('Calendar Table 2')
        ,'Calendar Table 2'[DATE] <= MAX('Calendar Table 2'[DATE]))
        , 'Final Spend'[CATEGORY] <> "Month Budget"
        , 'Final Spend'[CATEGORY] <> "Spend Delta")

 

That said, I have a couple follow-up question...

Why did SUMX work for me rather than SUM?  I am still a little confused about the difference between the two.   I am still trying to understand how these two differ in what/how they output.

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.