Forum Discussion

sumitnegi1266's avatar
sumitnegi1266
Frequent Visitor
3 years ago
Solved

How to cumulative sum with condition

Hi,

I have below data and need to now calculate the cumulative sum of forecast value at a certain date. I have a days measure which I am adding with the date e.g. 16th Aug + 4 days = 20th Aug. I then need to calculate Cumulative sum for 20th Aug, which should be 160. How to calculate it using dax?

 

Appreciate any help here!

  • This can be accomplished either in a measure to be used in a table,  or in a calculated column in PowerBI's Data View.  The measure, when applied to a table - with the Date column added -  will result in this:

    The column added in the Data View will result in this:

     

    Here is the code I used:
    Measure

    Cumulative Sum = 
    VAR current_date = SELECTEDVALUE('Table'[Date])
    RETURN
    
    CALCULATE( SUM('Table'[Forecast Value]), 'Table'[Date] <= current_date)
    // Returns the sum for all dates less than or equal to the date on the current row.

    Column

    Running total = 
    VAR current_date = 'Table'[Date]
    RETURN
    
    CALCULATE( SUM('Table'[Forecast Value]), ALL('Table'), 'Table'[Date] <= current_date)
    // Returns the sum for all dates less than or equal to the date on the current row.

     

    Also, If you wanted it to count up differently based on certain criteria - like when it becomes a new year - I would suggest using the column method and doing something like the following:

     

    I hope this helps!  If it does, please mark this as the solution.  Kudos are appreciated 🙂

5 Replies

  • This can be accomplished either in a measure to be used in a table,  or in a calculated column in PowerBI's Data View.  The measure, when applied to a table - with the Date column added -  will result in this:

    The column added in the Data View will result in this:

     

    Here is the code I used:
    Measure

    Cumulative Sum = 
    VAR current_date = SELECTEDVALUE('Table'[Date])
    RETURN
    
    CALCULATE( SUM('Table'[Forecast Value]), 'Table'[Date] <= current_date)
    // Returns the sum for all dates less than or equal to the date on the current row.

    Column

    Running total = 
    VAR current_date = 'Table'[Date]
    RETURN
    
    CALCULATE( SUM('Table'[Forecast Value]), ALL('Table'), 'Table'[Date] <= current_date)
    // Returns the sum for all dates less than or equal to the date on the current row.

     

    Also, If you wanted it to count up differently based on certain criteria - like when it becomes a new year - I would suggest using the column method and doing something like the following:

     

    I hope this helps!  If it does, please mark this as the solution.  Kudos are appreciated 🙂

  • sumitnegi1266's avatar
    sumitnegi1266
    Frequent Visitor

    kameronyork Thanks for you response. The date I am using is a calculated date which is a measure. I tried using below but got an error i.e. "Visual has exceeded the available resources"

     
    Cumulative Calc =
    var _date = [Date to hold]
    return
    CALCULATE( SUM('Forecast Sales'[Forecast Value]), 'Forecast Sales'[Date] <= _date)
     

     

    • kameronyork's avatar
      kameronyork
      Icon for Resolver I rankResolver I

      Would you be willing to provide the measure you are using for the date?  I will then do some testing to find the best solution! 🙂

      • sumitnegi1266's avatar
        sumitnegi1266
        Frequent Visitor

        kameronyork Below is the date measure I am using. Max('Lead Times'[LeadTime]) is the number of days I am using from another table which has a relationship with current table