Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Cumulative Sum - Dax Query

 

Hello. I am trying to create a chart that shows the Cumulative sum of Apps Created over time.

 

How can I do it with Dax query ?

 

So far, I created the below chart which gives the Count of Apps created particular month !

  • Y axis -  'App'[admin_appid]
  • X axis -  'App'[admin_appcreatedon]
 

 

Kindly let me know how to create and plot the Cumulative sum of Apps Created over time.

 

General Comment
Need Help
Tips and Tricks
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    PLease try

    Cumulative Sum =
VAR CurrentDtate =
    MAX ( 'App'[admin_appcreatedon] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'App'[admin_appid] ),
        'App'[admin_appcreatedon] <= CurrentDtate,
        'App'[admin_appdeleted] = "True",
        ALL ( 'App' )
    )

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , You need to create a measure like

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

     

    or

     

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    HI Anonymous 
    PLease try

     

    Cumulative Sum =
VAR CurrentDate =
    MAX ( 'App'[admin_appcreatedon] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'App'[admin_appid] ),
        'App'[admin_appcreatedon] <= CurrentDate,
        ALL ( 'App' )
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello tamerj1 ,

       

      Thank you for responding. I did try the code, but something is not matching with Values.

       

      How to exclude values from Table 'App' where admin_appdeleted is equal to true from the below code ?

       
      Cumulative Sum =
VAR CurrentDtate =
    MAX ( 'App'[admin_appcreatedon] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'App'[admin_appid] ),
        'App'[admin_appcreatedon] <= CurrentDtate,
        ALL ( 'App' )
    )

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Hi Anonymous 
        PLease try

        Cumulative Sum =
VAR CurrentDtate =
    MAX ( 'App'[admin_appcreatedon] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'App'[admin_appid] ),
        'App'[admin_appcreatedon] <= CurrentDtate,
        'App'[admin_appdeleted] = "True",
        ALL ( 'App' )
    )