cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rsbin
Post Partisan
Post Partisan

Cumulative Totals: Last Year vs Current Year

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!

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User III
Super User III

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.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@sevenhills, @amitchandak , @Ashish_Mathur 

Thank you all for the responses.   All of the new suggestions work as intended.

rsbin_0-1602940051675.png

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!

 

amitchandak
Super User IV
Super User IV

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

sevenhills
Impactful Individual
Impactful Individual

 

Cumulative Volume = CALCULATE( [Total Volume],
                      FILTER(ALLSELECTED('Calendar'),
                      'Calendar'[Date] <= MAX('Calendar'[Date] )
                      && ( 'Calendar'[Year] = 2019 || 'Calendar'[Year] = 2020)
                      )
                     )

 

@sevenhills 

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?

 

 

rsbin_0-1602893015588.png

 

Thanks again for the quick reply.  Much appreciated!

sevenhills
Impactful Individual
Impactful Individual

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/

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors