Forum Discussion

ShemRo's avatar
ShemRo
Regular Visitor
4 years ago
Solved

Cumulative Sum not working??

Hi There,

I have the below dataset.

I am wanting to calculate a cumulative total of the act column. I am making a mistake in my calculated column somwhere in the code:

Cumulative_Act_Hours_Col = CALCULATE(SUM('Table'[act]),Filter(All('Table'[MonthYear]),'Table'[MonthYear]<=EARLIER('Table'[MonthYear])))

 

Any helpwould be greatly appreciated. I have done similar formulas before but this one has got me stumped.

 

Thanks in advance.

Need Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    Oh Ok.

    Have you tried this:

    Cumulative_Act_Hours_Col =
    CALCULATE(
    SUM( 'Table'[act] ),
    FILTER( ALL( 'Table' ), 'Table'[MonthYear] <= EARLIER( 'Table'[MonthYear] ) )
    )

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

6 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi ShemRo 

     

    What is your "MonthYear" column format? it seems it's not date/time!

    Check that and then if it's not Date format change that to Date format and check your formula again.

     

    Cumulative_Act_Hours_Col = CALCULATE(SUM('Table'[act]),Filter(All('Table'),'Table'[MonthYear]<=EARLIER('Table'[MonthYear])))

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

    • ShemRo's avatar
      ShemRo
      Regular Visitor
      4 years ago

      That it what i orginally thought too but...

      regards Shem

      • VahidDM's avatar
        VahidDM
        Super User
        4 years ago

        Oh Ok.

        Have you tried this:

        Cumulative_Act_Hours_Col =
        CALCULATE(
        SUM( 'Table'[act] ),
        FILTER( ALL( 'Table' ), 'Table'[MonthYear] <= EARLIER( 'Table'[MonthYear] ) )
        )

         

        If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
        Appreciate your Kudos!!
        LinkedIn:         www.linkedin.com/in/vahid-dm/

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    This is quick and simple. 

    cumulativeAmt = VAR _dateScope=MAX('Table'[dt])
return CALCULATE(SUM([act]),ALL('Table'),'Table'[dt]<=_dateScope)

     

     

    • ShemRo's avatar
      ShemRo
      Regular Visitor
      4 years ago

      Thanks Anonymous 

      ..Hmm... something is off as mine only sum and dont accumulate: