Cumulative Sales by Customer using Play Axis

[Anonymous]

Hi Everyone,

I posted a similar question a little while ago but never was able to get to a solution.  I am looking to show cumulative sales by customer since January 1, 2018 on a bar chart using the play axis to see how the customers compare over time.  I have a cumulative measure that shows the cumulative sales over that time and it is correct.  That measure is,

Cumulative Invoiced CAD =
calculate([Total Revenue $$$ (CAD)],
Filter(allselected(CalendarDate),
CalendarDate[Date]<=max(CalendarDate[Date])))

In my last posting, it was suggested I use the following measure instead.  

Cumulative Invoiced for PLAY =
CALCULATE(
[Total Revenue $$$ (CAD)],
DATESBETWEEN(
CalendarDate[Date],
MIN(CalendarDate[Date]),
MAX(CalendarDate[Date])
)
)

Both measures give me correct values overall.  

My issue is that the sales by customer are shown for *each* day for both measures shown above.  So when I play the visualizations, they reset for *each* day.  The values for each day are correct, but it is not showing the accumlation of the sales.  For example, the bar chart is blank on weekends because we do not invoice on weekends and if a customer is given a credit their sales for that day are negative.

The interactions with the bar chart is set to "filter".

It must be a setting with Play or some sort of cross filtering causing the cumulative to reset each day.  Any help would be appreciated. 

Cheers!

[Anonymous]

That worked wonderfully!  Thank you.

For future viewers of this form, there is a typo in the DAX code proposed.  It should say "ALLSELECTED".

Thanks again!

[Anonymous]

Could you please describe what did you exactly do here? I've been trying for about few days already but nothing seems to be working with Play Axis.

Please see the photo bellow. What I'm aiming to do here is to hit 'Play' Button and show title race in EPL 2022-23. Now you see number of points as of now. However, when I click play Axis it shows me number of points at one certain date, not cumulative number. 

[Anonymous]

Also, please not that for the visual I'm using not Horizontal Bars but table, bars are coming from conditional formating as it was the only way to show teams icons.

[amitchandak · Accepted solution]

@Anonymous , I think you have  to use an independent date table for the slicer on which play axis will run

Example

Cumulative Invoiced for PLAY =

var _max = MAXX(allselceted(Date2) , Date2[Date])

return
CALCULATE(
[Total Revenue $$$ (CAD)],
DATESBETWEEN(
CalendarDate[Date],
MIN(CalendarDate[Date]),
MAX(CalendarDate[Date])
) , CalendarDate[Date] <=_max
)

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI