Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

DAX formula to Calculate Column Cumulative Total

Hello, I am trying to add a calculated column to the dataset so that I can plot a line graph that shows cumulative total cost by week or month. It this possible given that there are several lines that makes the total for the week?

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi, Thank you, I couldn't not make it work, but the following worked:

     

    New Measure

     

    Running Total=

    CALCULATE(Sum(Table[Total Cost]),

                    ALL('Table'),Table[Work Date]<=EARLIER(Table[Work Date]))

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Basically something like this:

     

    Cumulative Total = 
VAR myDate = MAX([Work Date])
VAR myMonth = MONTH(myDate)
VAR tmpTable = FILTER(ALL(Table),[Month no.] <=myMonth)
RETURN SUMX(tmpTable,[Total Cost])
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi, Thank you, I couldn't not make it work, but the following worked:

       

      New Measure

       

      Running Total=

      CALCULATE(Sum(Table[Total Cost]),

                      ALL('Table'),Table[Work Date]<=EARLIER(Table[Work Date]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hello, 

     

    I am trying to develop a measure to calculate cumulative total for a specific year, in that case, for 2019. and I am doing this formula but I doesn't work: 

     

    VAR CUMULATIVE2019 =
    CALCULATE([Sales];DATESBETWEEN('Calendar'[year];DATEADD(LASTDATE('Calendar'[year]);1;YEAR);LASTDATE('Calendar'[year])))
    RETURN
    IF([Sales]<>BLANK();CUMULATIVE2019)
     
    Can you help me please? 
     
    Thank you

     

    • hyperbob0811's avatar
      hyperbob0811
      Frequent Visitor
      6 years ago

      none of these solutions work, when you do not want to use a date.  I have an index column and it still doesnt work.  I get the Earlier/Earliest refers to an earlier row which doesn't exist. error.