Retention Condition measures

[Anonymous]

Hi, I have been posting this same problem but could not solve it yet.

I attached my demo power bi file in the googld doc link.

So, I need to get customer who meets at least one of the 3 conditions below.

Let's say our company is doosan.

case 1. customer purchased from apple within last 24 months but did not purchased any other companies' products for 24 months.

case 2. customer bought apple 3 years ago but did not make any purchases from other companies.

case 3. customer bought apple first 3 years but did not make any purchases from other companies.

	2015	2016	2017	2018	2019	2020
Customer 1	x	x	x	x	Doosan	x
Customer 2	x	x	x	x	x	Doosan
Customer 3	x	x	Doosan	x	x	x
Customer 4	cat	Cat	cat	doosan	x	x
Customer 5	x	doosan	doosan	doosan	cat	doosan
Customer 6	doosan	doosan	doosan	x	x	x
Customer 7	x	x	cat	doosan	doosan	doosan
Customer 8	x	x	x	doosan	doosan	x

Customer 1, 2, 3, 4, 6, 7, 8 are retained customer and 5 is not because customer bought microsoft product on 2019.

But if I make table that is filtered on year 2015, customer 5 is retained and if customer 5 bought microsoft on 4/1/2019, then customer should be marked as a retained customer before 4/1/2019. 

If any of apple customer bought other companies' product also cannot be a retained customer.

I have calculated some measures to get those cases.

Total Doosan = CALCULATE(DISTINCTCOUNT('UCC DATA'[UCC Serial Num]), 'UCC DATA'[Manufacturer]="DOOSAN")

-> This formula shows me the total number of Doosan equipment that a customer bought.

case 1 = IF(CALCULATE([Total Doosan],DATESINPERIOD(dCalendar[Date],MAX(dCalendar[Date]),-24,MONTH))>=1,1,0)

-> This one is the first condition. It seems working fine but I wanted get this one checked to see if there are outliers.

Case 2 = IF(CALCULATE(SUM('Customer Master'[UCC - All New Units]), DATESINPERIOD('dCalendar'[Date],MAX('dCalendar'[Date]),-23,MONTH))<1 && CALCULATE([Total Doosan], DATESINPERIOD('dCalendar'[DATE],EDATE(MAX('dCalendar'[Date]),-23),-12,MONTH))>=1,1,0)

-> This one I do not think it is working properly but I got a feeling that this formula needs revision.

I haven't started case 3 but can anyone be able to check if I am on track and the formula is used correctly?

Here is the link that you can download my demo file.

https://drive.google.com/file/d/1k10VsQ2r3H_-L-3T2k0An9NajyoS0V0z/view?usp=sharing

Thank you.

[Greg_Deckler]

@Anonymous - Chapter 6, recipes 2 and 3 deal with customer KPI's around new, returning, retained, lost customers. 

https://github.com/gdeckler/DAXCookbook

[Anonymous]

@Greg_Deckler 

Hey, Thank you so much for reply. But I try to apply on my pbi but it took so long time to visualize...

Case1 =

var _2year =

DISTINCT(

SELECTCOLUMNS(

FILTER(

ALL('UCC DATA'),

[Date] < MAX('UCC DATA'[Date])-730

),

"Last 2 Year",'UCC DATA'[Customer]

)

)

Var NotDoosan =

DISTINCT(

SELECTCOLUMNS(

FILTER(

'UCC DATA','UCC DATA'[Manufacturer]<>"DOOSAN"

),

"NotDoosan", 'UCC DATA'[Customer]

)

)

Var DoosanCustomer = EXCEPT(_2year,NotDoosan)

Var _Count = COUNTROWS(DoosanCustomer)

Return

IF(ISBLANK(_Count),0,1)

This is my formula but it does show visualize and keep showing the loading sign on the top left corner of the visual. 

How can I solvel this problem?

[Greg_Deckler]

@Anonymous  - Difficult to say. I have some blog articles around DAX performance tuning. 

https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275

https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-2/ba-p/976813

Also, @marcorusso has some patterns on daxpatterns.com for this sort of stuff as well. Should have mentioned those initially.