Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Help URG Customer Analysis

Hi, I'm new to power bi and I have a problem. I'm trying to create three customer measures: New: Customer without historical sales since 2017 Recovered: Customer without sales in all of 2022 and wi...
amitchandak's avatar
amitchandak
Super User
3 years ago

Anonymous 

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

 

No Sale 2017 = CALCULATE(SUM(Sales[Sales Amount]), Date[Year]>= 2017 && Date[Date] <= emonth(Max(Date[Date]), -1) )

 

New customer = countx(Values(Customer[Customer]), if( isblank([No Sale 2017]) && Not(isblank([MTD Sales])) , [Customer], blank()) )

 

for 2022, 2023, use loging to keep it dynamic 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-2,Year))

 

New customer = countx(Values(Customer[Customer]), if( isblank([Rolling 12 ])  , [Customer], blank()) )

 

all sales= CALCULATE(SUM(Sales[Sales Amount]),all())

 

Lost = countx(Values(Customer[Customer]), if( isblank([all sales])  , [Customer], blank()) )

 

 


Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

 

Learn Power BI Advance - Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q