cancel
Showing results for
Did you mean:
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
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

``````Cumulative Volume = CALCULATE( [Total Volume],
FILTER(ALLSELECTED('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date] && year('Calendar'[Date]) = Year(MAX('Calendar'[Date]) )))``````

Tutorial Series Dax Vs SQL Direct Query PBI Tips

Proud to be a Super User!

6 REPLIES 6
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
Post Partisan

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!

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

``````Cumulative Volume = CALCULATE( [Total Volume],
FILTER(ALLSELECTED('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date] && year('Calendar'[Date]) = Year(MAX('Calendar'[Date]) )))``````

Tutorial Series Dax Vs SQL Direct Query PBI Tips

Proud to be a Super User!

Impactful Individual

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

Post Partisan

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!

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

Announcements

#### Microsoft named a Leader in The Forrester Wave

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