Need Help in creating running total in cohort analysys

[sudheerbolla]

Hi everyone, 

I am completely new to Power BI, Could someone help in in creating running total in cohort analysis please. Thanks in advance

Policy Attrition Count:

The following table obtained:

POLICY ATTRITION MATRIX (COHORT ANALYSIS)

Description of output: In Apr-2023, 1456 no of new policies were taken, out of 1456 new policies, 20 no of policies were cancelled in Apr-2023, and 63 no of policies were cancelled in May-2023 excluding the 20 no of policies cancelled in Apr-2023.

The above output obtained by using the following DAX function

Policy Attrition =

VAR vCurrentMonthAfter = SELECTEDVALUE(Tenure_Table[Value])

VAR vCurrentCohort = SELECTEDVALUE(Consolidated[Cohort])

VAR vleft =

CALCULATE(

    [NewPolicies],

    FILTER(

        Consolidated,

        EOMONTH(Consolidated[PolicyCancelDateColumn],0)=EOMONTH(vCurrentCohort,vCurrentMonthAfter)

    )

)

RETURN

vleft

Tenure_Table = GENERATESERIES(0,24,1)

Cohort =

VAR vPolicyNo = Consolidated[POLICY_NUMBER (Imported)]

VAR vResult = CALCULATE(

    EOMONTH(MIN([PolicyPurcDateColumn]),0),

    FILTER(Consolidated,Consolidated[POLICY_NUMBER (Imported)]=vPolicyNo)

)

RETURN

vResult

NewPolicies = DISTINCTCOUNT(Consolidated[POLICY_NUMBER (Imported)])

How to get the cumulative policies cancelled output as below. How to write the dax function to get the following output

POLICY RETENTION MATRIX (COHORT ANALYSIS)

The above out put will help  me in creating the retention matrix, by substracting the retention matrix values from the new policies count.