Forum Discussion

AndrejZitnay's avatar
AndrejZitnay
Post Patron
8 years ago
Solved

Normal cumulative sum

Hello all,

 

I want to show normal cumulative within line chart.

 

My Axis has to be 3 years hence I use this :

 

MonthYear Retention breakdown = FORMAT(Terminations[Date of Renewals],"mmm")&"-"&FORMAT(Terminations[Date of Renewals],"YY")

 

My Current value is Measure :

 

Retention = CALCULATE(COUNTROWS(Terminations),FILTER('Terminations','Terminations'[Renewals?]="Renewals"))

 

Can you help with DAX formula for simple cumulative where I can visual in my chart?

 

This is current layout :

 

 

 

Many thanks.

 

Kind regards.

 

Andrej

 

 

 

 

 

 

Need Help
  • Daniil's avatar
    Daniil
    8 years ago

    Of course... :-)

     

    You just need to also include the column you are sorting by in ALL. Like so:

    Retention Cumulative =
CALCULATE (
    [Retention],
    FILTER (
        ALL (
            Terminations[Date of Renewals],
            Terminations[MonthYear Retention breakdown],
            Terminations[SORT COLUMN OF MonthYear Retention breakdown]
        ),
        Terminations[Date of Renewals] <= MAX ( Terminations[Date of Renewals] )
    )
)

    Does this make sense?

5 Replies

  • Daniil's avatar
    Daniil
    Kudo Kingpin
    8 years ago

    Try this:

    Retention Cumulative =
CALCULATE (
    [Retention],
    FILTER (
        ALL (
            Terminations[Date of Renewals],
            Terminations[MonthYear Retention breakdown]
        ),
        Terminations[Date of Renewals] <= MAX ( Terminations[Date of Renewals] )
    )
)
    • AndrejZitnay's avatar
      AndrejZitnay
      Post Patron
      8 years ago

      Hello Daniil,

       

      Thank you for trying to help.

       

      Unfortunately it didn't work.

       

      Outcome is exactly the same.

       

      Kind regards.

       

      Andrej

      • Daniil's avatar
        Daniil
        Kudo Kingpin
        8 years ago

        Of course... :-)

         

        You just need to also include the column you are sorting by in ALL. Like so:

        Retention Cumulative =
CALCULATE (
    [Retention],
    FILTER (
        ALL (
            Terminations[Date of Renewals],
            Terminations[MonthYear Retention breakdown],
            Terminations[SORT COLUMN OF MonthYear Retention breakdown]
        ),
        Terminations[Date of Renewals] <= MAX ( Terminations[Date of Renewals] )
    )
)

        Does this make sense?