Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good Evening,
Working with cumulative totals and having a hard time figuring out my last step.
I want to chart Cumulative Volumes for all of 2019 and then Cumulative volume YTD for 2020
I have been able to create Cumulative Volume for my entire data set as below:
Cumulative Volume = CALCULATE( [Total Volume],
FILTER(ALLSELECTED('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date] )))
I haven't been able to figure out how to separate running totals for 2019 from 2020. I figure it should be fairly simple.
I have [Year] in my 'Calendar' table but unable to get my FILTER syntax worked out.
Leaning for some assistance from the Community.
Best Regards and hope everyone is keeping safe!
Solved! Go to Solution.
@sevenhills , YTD, using datesytd or total YTD can serve the purpose
Example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
No need for Last YTD , as ytd will reset itself at year-end
or Try your formula like
Cumulative Volume = CALCULATE( [Total Volume],
FILTER(ALLSELECTED('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date] && year('Calendar'[Date]) = Year(MAX('Calendar'[Date]) )))
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
Hi,
Select 2020 in the Year slicer (which should be from the Calendar Table). To your visual, drag Month name from the Calendar Table. Try this measure:
=calculate([Total volume],datesytd(calendar[date],"31/12"))
Hope this helps.
@sevenhills, @amitchandak , @Ashish_Mathur
Thank you all for the responses. All of the new suggestions work as intended.
Now, my next step is to turn Oct - Dec 2020 (green line) into a budget line. I believe I have found a thread that describes how to do that.
Again, thank you all and best regards!
@sevenhills , YTD, using datesytd or total YTD can serve the purpose
Example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
No need for Last YTD , as ytd will reset itself at year-end
or Try your formula like
Cumulative Volume = CALCULATE( [Total Volume],
FILTER(ALLSELECTED('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date] && year('Calendar'[Date]) = Year(MAX('Calendar'[Date]) )))
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
Cumulative Volume = CALCULATE( [Total Volume],
FILTER(ALLSELECTED('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date] )
&& ( 'Calendar'[Year] = 2019 || 'Calendar'[Year] = 2020)
)
)
Thanks much for the reply. Formula works, but not quite what I need.
I need to reset 2020 Volume to 0 and start my cumulative total again. The two line charts should almost line up...of course until Sept which is the last month in my data.
Any idea on how to modify your dax to achieve this?
Thanks again for the quick reply. Much appreciated!
Provide the data sample ... as I dont see Volume in your first formula.
I am thinking what you need is DATESYTD, I may be wrong
Check this article: https://www.sqlbi.com/articles/computing-running-totals-in-dax/