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
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.
Solved! Go to Solution.
@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
@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:
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
91 | |
86 | |
76 | |
66 |
User | Count |
---|---|
149 | |
117 | |
111 | |
106 | |
95 |