NEED HELP IN GETTTING RUNNING TOTAL IN COHORT ANALYSIS

[sudheerbolla]

Hi every one, @Ashish_Mathur

Need running total in Cohort Analysis

an hour ago

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 ATTRITION MATRIX (RUNNING TOTAL ) (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.

https://www.dropbox.com/scl/fi/3qmevl7qq5h6671i050dr/OUTCOME-1.xlsx?rlkey=iywhx8nwsk8cg6r24sgvuetap&...

https://www.dropbox.com/scl/fi/l6chb5124ryh5ezcfw2ip/Sample-Data.xlsx?rlkey=mnv7cmihut6atk48lsm2nx8d...

[Ashish_Mathur · Accepted solution]

Hi,

Since you want the answer for April 2023 to be 1,456 and not 1,436, it is implied that you want to compute the number of active policies as on the first of the month.  If my assumption is correct, then please download my solution file.

Hope this helps.

[Ashish_Mathur]

How did you derive the answer of 1456 for April 2023?  What filters should i apply on the Excel file to get that number?

[sudheerbolla]

1456 is the no of new policies taken in APR-2023.

I USED CONDITIONAL COLUMN 

THIS IS THE OUTCOME I GOT BY ABOVE, 

I AM UNABLE TO GET 83 (20+63) INSTEAD OF 63 IN COLUMN 1 AGAINST THE ROW APR-2023 LIKE WISE FOR ALL THE ROWS OF MONTH-YEAR.

COULD YOU DO IT IN POWER BI. IF YOU ARE DOING IN EXCEL, CAN IT BE APPLIED SAME IN POWER BI

[Ashish_Mathur]

How did you arrive at 20 under the 0 column for April 2023?  I am assuming that means the policies that were bought and terminated in April 2023.  If my understanding is correct, then the number under that column should be 5.  How are you getting 20 there?

 

[sudheerbolla]

I USED THE FOLLOWING DAX FUNCTION TO GET THE POLICY CANCELATION DATE.

A POLICY NO (LETS SAY "X") CAN HAVE MULTIPLE RECORDS BASED ON THE TRASACTIONS HAPPENED TO THAT POLICY.

If the event is create_policy, record 1 is generated for policy no X

if the event is policy_renewal (after the policy term 6 months) 2nd record is generated for policy no X

if the event is update_policy (adding extra person to policy) 3rd record is generated for plicy no X

if the event is cancel_policy, 4th record is generated for policy no X.

PolicyCancelDateColumn = Var _PolicyNo = (Consolidated[POLICY_NUMBER (Imported)])

Return

IF(

    OR(

        (Consolidated[EVENT_CODE (Imported)])="cancel_policy",

        (Consolidated[EVENT_CODE (Imported)])="cancel_policy_non_payment"

    ),

MAXX(FILTER(ALL(Consolidated),Consolidated[POLICY_NUMBER (Imported)]=_PolicyNo),Consolidated[Crediting Date]),

BLANK()

)

[Ashish_Mathur]

Also, of all the different date columns available, just tell me which is the policy start date column and which is the end date column

[sudheerbolla]

Policy start date is the minimum date in crediting date for a policy no because policy no can have mulitple records when a policy under go renewal or cancellation or update.

policy end date is the maximum date in crediting date of the policy number for an event code "policy_cancel" or "policy_cancel_non_payment"

[Ashish_Mathur · Accepted solution]

Hi,

Since you want the answer for April 2023 to be 1,456 and not 1,436, it is implied that you want to compute the number of active policies as on the first of the month.  If my assumption is correct, then please download my solution file.

Hope this helps.

[sudheerbolla]

Thank you so much for your hard work, you are very near to the solution.

It should have the values as below

	total policies	january	february	march	april	may
Jan-23	410	399	389	370	358	349
Feb-23	544	527	509	492	473
Mar-23	1279	1243	1182	1117
Apr-23	1456	1436	1373
May-23	1581	1553

if total policies column is not possible, it is fine. Could you please try to get as above.
Thank you in advance

[sudheerbolla]

Can it be arranged in this way as shown below, the count of policies retained at end of the month.

		january	february	march	april	may
Jan-23		399	389	370	358	349
Feb-23		527	509	492	473
Mar-23		1243	1182	1117
Apr-23		1436	1373
May-23		1553

if total policies column is not possible, it is fine. Could you please try to get as above.
Thank you in advance

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached my solution file.

Hope this helps.

[sudheerbolla]

I have gone through your previous solution and it helped me understanding the approach to the solution. You have a vast knowledge in power bi. Thank you so much for your guidence.

I appreciate you. I am new to the Power BI.

Thank you

[Ashish_Mathur]

You are welcome.  How did you get the column headers as numbers i.e. 1,2,3?  Please share your PBI file.

[sudheerbolla]

Policy start date is the minimum date in crediting date for a policy no because policy no can have mulitple records when a policy under go renewal or cancellation or update.

policy end date is the maximum date of the policy number for an event code "policy_cancel" or "policy_cancel_non_payment"

[Ashish_Mathur]

Let's not focus on the DAX formulas for now.  What would you treat as the cancellation date of policy number TANDBZLV9C?  Will it be October 27, 2023?  Please also show in the Excel file (no DAX formulas) as to how you arrived at 20 under column 0 for April 2023?

[sudheerbolla]

yes the cancellation date is October 27, 2023 (to be taken from the crediting date column against the policy no TANDBZLV9C

[Ashish_Mathur]

Hi,

As per my approach of solving this question, there should exist one row for each month of every policy from the beg to the end date.  So for example, policy TA2M2RFQL8 has only one row and this policy is effective from May 2023 to Nov 2023.  My approach would be to split this into 7 rows - one for each month.  This will lead to an increase in th size of the table.  If you are OK with this approach, then i can try out my solution.

[sudheerbolla]

No problem, you can split it. Thank you very much