Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Cumulative or Rolling total issue

I am trying to do a simple cumulative total using the below measure and having trouble.  The cumulative total is not adding up if i try the DATESMTD or DATESYTD functions. What am i missing 

 

cumulative Total = calculate(sum(Trans[Amount]),DATESYTD('calendar'[Date]))
 
DateSerialAmountcumulative Total
2022-01-1058812001200
2022-01-2072214001400
2022-02-0282025002500
    

 

Expected result is 

 

DateSerialAmountcumulative Total
2022-01-1058812001200
2022-01-2072214002600
2022-02-0282025005100

 

PBix is  https://drive.google.com/file/d/12kfwDvcXWNX3FTWLcOLNBCscRmsGPyoO/view?usp=sharing

 

Thanks

 

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 

    Then you have to have an index or ranking column. In other words, for each date-serial combination there must be a unique sort order number column. There a method to do that using dax it is preferable to be done using power query. 

9 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 
    Refer to the sample file attached 

    cumulative Total = 
VAR CurrentDate =
    MAX ( 'calendar'[Date] )
VAR CurrentSerial =
    MAX ( Trans[Serial] )
VAR Result =
    CALCULATE ( 
        SUM ( Trans[Amount] ), 
        'calendar'[Date] <= CurrentDate,
        Trans[Serial] <= CurrentSerial,
        VALUES ( 'calendar'[Year] )
    )
RETURN
    Result
    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Hi Anonymous 

      have you tried this solution?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thanks tamerj1 . Let me try this out how it behaves with duplicate serials 

  • davehus's avatar
    davehus
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi Anonymous ,

     

    Because you have the serial in the table, this is causing the issue with the RT not working for you. Please use the measure below, it should work.

     

     

     

    cumulative Total = calculate(sum(Trans[Amount]),ALL(Trans),DATESYTD(Trans[Date]))
     
    Use the date column from the trans table in the matrix.
     
    Did I help you today? Please accept my solution and hit the Kudos button.
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks davehus . Appreciate the help

       

      That worked. Only issue is if i have the serials for same day, the cumulative totals repeats. How can it be treated as mor as running totals

       

      1400 & 1000 for Jan 20th, results in 3600 repeated for the day

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks Ashish_Mathur 

      The total breaks if i have the serial not in the seqeuntial order. 

      I tried ignoring the serial, but then if there are multiple serials for the same day the total repeats. 

      Also i modified the total to be the fiscal year total that ends 04/30. 

      Attached my pbix - https://drive.google.com/file/d/1qX1zUOhgj_NeSd-IvNaIlBtt6zccJJhT/view?usp=sharing

       

      Trying the fiscal total that ends at 04/30. If i run report upto sept 2021, total will be from May to Sep 2021