Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I would like to create a measure or new column that identifies a lost customer.
A lost customer is defined as a customer that haven't made a purchase for 4 months.
If after 4 months the customer makes a purchase again it is not a lost customer anymore.
I have had a simular topic regarding new customers, which can be found here
I am using the follwing data:
(Last column in red is the desired column)
Company | Company Nr | Product | Purchase date | Value | Loss customer |
Company A | 1111 | Soda | 12-1-2018 | 100 | Loss |
Company A | 1111 | Soda | 24-2-2018 | 70 | Loss |
Company A | 1111 | Beer | 8-3-2018 | 145 | Loss |
Company A | 1111 | Water | 12-3-2018 | 293 | Loss |
Company B | 223311 | Beer | 12-5-2017 | 80 | |
Company B | 223311 | Beer | 13-6-2017 | 200 | |
Company B | 223311 | Beer | 24-6-2017 | 40 | |
Company B | 223311 | Water | 24-5-2018 | 30 | |
Company B | 223311 | Soda | 12-6-2018 | 60 | |
Company B | 223311 | Soda | 31-7-2018 | 50 | |
Company B | 223311 | Beer | 2-8-2018 | 400 | |
Company C | 33229153 | Soda | 22-2-2017 | 23 | Loss |
Company C | 33229153 | Beer | 25-5-2017 | 51 | Loss |
Company C | 33229153 | Soda | 12-8-2017 | 175 | Loss |
Company C | 33229153 | Water | 23-11-2017 | 46 | Loss |
Company C | 33229153 | Soda | 7-1-2018 | 34 | Loss |
Company C | 33229153 | Beer | 16-2-2018 | 25 | Loss |
Furtheremore I have to be able to include or exclude customers manually.
Like the DAX for new customers has as well:
New Customer = VAR CurrentYear = 2017 VAR PriorYear = CurrentYear - 1 VAR FirstPurchaseCurrentYear = CALCULATE ( MIN ( Table1[Date] ), FILTER ( ALLEXCEPT ( Table1, Table1[Company] ), OR ( YEAR ( Table1[Date] ) = CurrentYear, AND ( YEAR ( Table1[Date] ) = PriorYear, MONTH ( Table1[Date] ) = 12 ) ) ) ) VAR LastPurchasebeforeCurrentYear = CALCULATE ( MAX ( Table1[Date] ), FILTER ( ALLEXCEPT ( Table1, Table1[Company] ), Table1[Date] < DATE ( PriorYear, 12, 1 ) ) ) VAR SalesGap = DATEDIFF ( LastPurchasebeforeCurrentYear, FirstPurchaseCurrentYear, MONTH ) RETURN IF ( AND ( SalesGap > 12 || SalesGap = BLANK (), OR ( YEAR ( Table1[Date] ) = CurrentYear, AND ( YEAR ( Table1[Date] ) = PriorYear, MONTH ( Table1[Date] ) = 12 ) ) ), "New Customer" )
Hope you can help me.
If any questions please do not hesitate to ask!
Solved! Go to Solution.
See if this will help get you on the right track.
First, I calculated the maximum purchase date for each customer:
Max Sales Date = CALCULATE( MAX(Sales[Purchase date]), ALLEXCEPT(Sales,Sales[Company]) )
Then I calculated the duration in months from today's date to [Max Sales Date] above:
Duration = DATEDIFF( [Max Sales Date], TODAY(), MONTH )
Then I made a simple IF() comparison:
Lost Customer = IF( [Duration] > 4, "Lost", BLANK() )
That gave me a table that looks like this:
You can hide the intermediate measures, or combine them into one big complex measure if desired.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @Anonymous,
Could you mark the proper answer as a solution please?
Best Regards,
Dale
See if this will help get you on the right track.
First, I calculated the maximum purchase date for each customer:
Max Sales Date = CALCULATE( MAX(Sales[Purchase date]), ALLEXCEPT(Sales,Sales[Company]) )
Then I calculated the duration in months from today's date to [Max Sales Date] above:
Duration = DATEDIFF( [Max Sales Date], TODAY(), MONTH )
Then I made a simple IF() comparison:
Lost Customer = IF( [Duration] > 4, "Lost", BLANK() )
That gave me a table that looks like this:
You can hide the intermediate measures, or combine them into one big complex measure if desired.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingNot as written, but why would you want to? In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
106 | |
104 | |
89 | |
65 |