Accumulated values for Cohort Visualization

[Anonymous]

Hi Everyone, I'm Trying to make a Cohort Viz, but something has been bugging me for a while.

The table i'm using has client data, so i am not able to share it. But i'm dealing with users that has to pay monthly fees, and one of its visualizations is the Churn Cohort, which means is the people that drops out of the contract.

Its rules goes as follows:
Whenever someone drops out of the contract, check the month the user dropped, and check which parcel the user dropped. The user will then be accounted for in these 2 criteria, as shown above.

But, i want to transform the measure into a Accumulative Measure, but I am not pulling this through.
the visual i'm looking for is something like this:
.....
Date = M1 | M1+M2 | M1 + M2 + M3 |  . . .  | Mn 

What am I doing it wrong in here?

Here's the measure:

Cohort Churn = 
VAR primario = 
    CALCULATE(
        [qtd_mensalidades],
        FILTER(
            fMensalidades,
            [mes_venda] <= TODAY() &&
            [numero_mensalidade] = DATEDIFF([mes_venda], [mes_devolucao], MONTH) + 1 &&
            [status] = "Devolvido"
        )
    )

RETURN 
 primario 

for some info about the table:

[month_sold] is the month the user signed the contract
[month_dropped] is the month the user dropped out
[parcel_number] is the number of the parcel of that entry.
[status] is the status of that user. can be dropped, active, late, etc.
The table has data of all the parcels the user has to pay, so in example:

the user 90182 signed a contract, now in the database it wil be created automatically all the parcels and thates the user has to pay, something like this:

simulation_id	parcel_number	month_sold	month_returned	status	expected_month_payment	month_payment
90182	1	1/10/2024	1/12/2024	Dropped	1/10/2024	1/10/2024
90182	2	1/10/2024	1/12/2024	Dropped	1/11/2024	1/11/2024
90182	3	1/10/2024	1/12/2024	Dropped	1/12/2024	1/12/2024
90182	.	.	.	.	.	.
90182	12	1/10/2024	1/12/2024	Dropped	1/10/2025

in this scenario, the user would be in accounted for in Dez 2024 in Month 3.

[Anonymous · Accepted solution]

Problem was, in the measure was accounting for only the parcel number associated with the month, not joining with other parcels. So, I needed operator logic to accont for that. Thought that fMensalidades[numero_mensalidade] <= DATEDIFF(fMensalidades[mes_venda],fMensalidades[mes_devolucao], MONTH) + 1 would work, but it was aggregating values only onto the 1st parcel. So i did the opposite:

Cohort Churn =

VAR Primario =

    CALCULATE(

        [qtd_mensalidades],

        FILTER(

            fMensalidades,

            fMensalidades[mes_venda] <= TODAY() &&

            fMensalidades[numero_mensalidade] >= DATEDIFF(fMensalidades[mes_venda], fMensalidades[mes_devolucao], MONTH) + 1 &&

            fMensalidades[status] = "Devolvido"

        )

    )

-- Check if there is any parcels in the current model

VAR ValidacaoParcela =

    CALCULATE(

        COUNTROWS(fMensalidades),

        FILTER(

            fMensalidades,

            fMensalidades[mes_venda] <= TODAY() &&

            fMensalidades[numero_mensalidade] <= DATEDIFF(fMensalidades[mes_venda], TODAY(), MONTH)

        )

    )

-- Return if available

RETURN

    IF(ValidacaoParcela > 0, Primario, BLANK())

[Anonymous]

Hi, @Anonymous 

Perhaps you can try to modify the Measure to the following formula:

Cohort Churn Accumulated = 
VAR CurrentMonth = MAX(fMensalidades[mes_devolucao])
RETURN
    CALCULATE(
        SUMX(
            FILTER(
                fMensalidades,
                [mes_devolucao] <= CurrentMonth &&
                [status] = "Devolvido"
            ),
            [qtd_mensalidades]
        )
    )

Please check if this Measure meets your calculation needs.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

not quite.

all the values in the table ends up with all lines filled, and it does not relfect the accumulated sum.

I tried the following, and it worked only in certain instances...