Need running total in Cohort Analysis

[sudheerbolla]

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.

[amitchandak]

@sudheerbolla , Make sure the Month year is coming from the date table and, number is coming from Tenure_Table table in matrix 

Then try a measure like

Cumm over Tenure_Table

calculate([NewPolicies], filter(all(Tenure_Table), Tenure_Table[Value] <= Max(Tenure_Table[Value])))

You can also consider the window function

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

[sudheerbolla]

I appreciate you reply, thank you very much. My doubt is:

how to connect the tenure table to the main consolidated table. To which parameter in consolidated table the value attribute in tenure table to be matched. Here is my model

my consolidated table

[sudheerbolla]

I appreciate you reply, thank you very much. My doubt is:

how to connect the tenure table to the main consolidated table. To which parameter in consolidated table the value attribute in tenure table to be matched. Here is my model

my consolidated table

[sudheerbolla]

I appreciate you reply, thank you very much. My doubt is:

how to connect the tenure table to the main consolidated table. To which parameter in consolidated table the value attribute in tenure table to be matched. Here is my model

my consolidated table