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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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