Retention percentage calculation in Dax

[azeezj]

Hello,

I have a contracts table that looks like the below:

CustomerID	ContractNumber	StartDate	SettleDate
1	34	2018-02-23	2023-02-10
1	89	2023-02-06	NULL
2	12	2023-01-31	2023-06-15
2	56	2020-09-21	2022-11-22
2	76	2021-05-13	2023-02-01

I want to determine retention using the below rule:

If the customer starts a new contract 6 months before their previous contract is settled, or 3 months after then they are Retained. I have tried the below calculated column but it does not work accurately:

CSC Rentention Ind =
VAR CurrentContractStartDate = 'New Contracts'[StartDate]
VAR CurrentCustomerID = 'New Contracts'[CustomerID]
VAR SixMonthsBeforeEndDate =
CALCULATE (
MIN ( 'New Contracts'[SettleDate] ),
FILTER (
ALL ( 'New Contracts' ),
'New Contracts'[CustomerID] = CurrentCustomerID
)
) - 6 * 30
VAR ThreeMonthsAfterEndDate =
CALCULATE (
MIN ( 'New Contracts'[SettleDate] ),
FILTER (
ALL ( 'New Contracts' ),
'New Contracts'[CustomerID] = CurrentCustomerID
)
) + 3 * 30
RETURN
IF (
CurrentContractStartDate >= SixMonthsBeforeEndDate
&& CurrentContractStartDate <= ThreeMonthsAfterEndDate,
"Yes",
"No"
)

TIA

[Ashish_Mathur]

Hi,

Based on the data that you have shown, show the expected result.

[azeezj]

I am expecting the result to be like the below:

CustomerID	ContractNumber	StartDate	SettleDate	Retention Ind
1	34	2018-02-23	2023-02-10	No
1	89	2023-02-06	null	Yes
2	12	2020-09-21	2022-11-22	No
2	56	2021-05-13	2023-02-01	No
2	76	2023-01-31	2023-06-15	Yes

[Ashish_Mathur · Accepted solution]

Hi,

Try these calculated column formulas

Previous settlement date = CALCULATE(MAX(Data[SettleDate]),FILTER(Data,Data[CustomerID]=EARLIER(Data[CustomerID])&&Data[StartDate]<EARLIER(Data[StartDate])))

Test = or(and(Data[StartDate]>=EDATE([Previous settlement date],-6),Data[StartDate]<=[Previous settlement date]),and(Data[StartDate]<=EDATE(Data[Previous settlement date],3),Data[StartDate]>=Data[Previous settlement date]))

Hope this helps.

[lbendlin]

what's your primary key? CustomerID plus ContractNumber ?

[azeezj]

Correct. But the retention is calculated per CustomerID

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY3BDcAgDAN3yRtLsSkUZkHsv0ZTkBCfKGdflDGMliw/MeRscEF5gfIPdJtpS61fudeAVSkWalcO7xA3xDEhHanUnRNewPuJ80hvPTmReaCCxeb8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t, ContractNumber = _t, StartDate = _t, SettleDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"SettleDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "PrevSettle", each List.Max(Table.SelectRows(#"Changed Type",(k)=>k[CustomerID]=[CustomerID] and k[ContractNumber]<[ContractNumber])[SettleDate])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Retention", each [StartDate]>=Date.AddMonths([PrevSettle],-6) and [StartDate]<=Date.AddMonths([PrevSettle],3))
in
    #"Added Custom1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[lbendlin]

If the customer starts a new contract 6 months before their previous contract is settled, or 3 months after then they are Retained.

The "6 months before" part should not be needed. What if they renewed 7 months before the expiry?

Your question is missing the "when?" component. Do you want to show the retention status as of today?  By month?

[azeezj]

Hi. 7 months before is not considered as retention - strictly 6 months before and 3 months after. I want to show status by month.