Normal cumulative sum

[AndrejZitnay]

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

[Daniil]

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]

Hello @Daniil,

Thank you for trying to help.

Unfortunately it didn't work.

Outcome is exactly the same.

Kind regards.

Andrej

[Daniil · Accepted solution]

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]

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

[AndrejZitnay]

Hello @Daniil

It makes now and now it works great.

Many thanks for your help.

Kind regards.

Andrej