Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cumulative Sales by Customer using Play Axis

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!
 
 
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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.