Forum Discussion
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 with sales in 2023
Lost: Customer without sale from 2021 to 2023, with vta. in previous years (2017-2020)
I have the fields customer, total dollars, date, year, etc.
Please, help me to identify the correct dax formula that gives me values that are not true.
1 Reply
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/1377458Learn Power BI Advance - Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q
