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")&"-...
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?

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?

    • AndrejZitnay's avatar
      AndrejZitnay
      Post Patron
      8 years ago

      Hello Daniil

       

      It makes now and now it works great.

       

      Many thanks for your help.

       

      Kind regards.

       

      Andrej

    • AndrejZitnay's avatar
      AndrejZitnay
      Post Patron
      8 years ago

      Hello Daniil,

       

      Your solution worked fine for me.

      I am now doing something similliar but instead of months I am calculating cumulatives this based on  diffrence of two months [Completion_Vs_Contract] this is as well in my axis however I have there some gaps and cumulative might go down in gap months.

       

      For example my monhts are -12 -11 -10 -9 -8 -3 -2 -1 0 1 2 3

       

      Is there way of taking this gaps into conisderation?

       

      Vintage Cumulative =
      CALCULATE (
          [Settlements],
          FILTER (
              ALL (
                  Terminations[Completion_Vs_Contract]
              ),
              Terminations[Completion_Vs_Contract] <= MAX ( Terminations[Completion_Vs_Contract] )
          )
      )

       

       

      Many thanks.

       

      Andrej