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.
Solved! Go to Solution.
Assuming the formula you had only need the date change
ClientRetention = VAR _today = max('Date'[Date]) VAR max1 = CALCULATE ( MAX ( 'Data'[Month]), ALLEXCEPT ( 'Data', 'Data'[ClientName] ) ) VAR min1 = CALCULATE ( MIN ('Data'[Month]), ALLEXCEPT ( 'Data', 'Data'[ClientName] ) ) RETURN IF ( DATEDIFF ( max1, _today, MONTH ) > 13, "Lost", IF ( DATEDIFF ( min1, _today, MONTH ) = 1, "New", "Retained" ) )
Hi @abloor ,
Here is my sample data.
You need to create a new measure.
Status = VAR max1 = CALCULATE ( MAX ( test[Date] ), ALLEXCEPT ( test, test[CustomID] ) ) VAR min1 = CALCULATE ( MIN ( test[Date] ), ALLEXCEPT ( test, test[CustomID] ) ) RETURN IF ( DATEDIFF ( max1, TODAY (), MONTH ) > 13, "Lost", IF ( DATEDIFF ( min1, TODAY (), MONTH ) <= 13, "New", "Retained" ) )
Here is the result.
Thanks @v-eachen-msft I think that might be working to some degree, but I need some help tweaking it please. Can you please advise me how to get it based on x months from slicer date, not x months from today? We may need to get this information retrospectively, not always based on what it is on day of report usage.
Also I made an error in my inital post. A 'New' client is actually one that has spent in the chosen slicer month, but NOT in the 12 prior months. e.g. they have spent in Aug 2019, but not Aug18-Jul19. How would I alter the line regarding a new client below to make this work?
Here is my code
Will taking a max of slicer date and use in place of Today(), solve the purpose?
VAR _today = max('Date'[Date])
Where data[date] is the date of the slicer.
Thanks for your quick reply @amitchandak
I'm only new to PBI and don't understand your solution. Can you please explain it in simple terms for a newbie?
e.g. if I need to change the dax in my above post, what bit do I change? Do I need to create any new measures or calc columns?
Much appreciated!!
Assuming the formula you had only need the date change
ClientRetention = VAR _today = max('Date'[Date]) VAR max1 = CALCULATE ( MAX ( 'Data'[Month]), ALLEXCEPT ( 'Data', 'Data'[ClientName] ) ) VAR min1 = CALCULATE ( MIN ('Data'[Month]), ALLEXCEPT ( 'Data', 'Data'[ClientName] ) ) RETURN IF ( DATEDIFF ( max1, _today, MONTH ) > 13, "Lost", IF ( DATEDIFF ( min1, _today, MONTH ) = 1, "New", "Retained" ) )
Hi,
daxpatterns.com has a pattern that I think will do well on what you are looking for:
https://www.daxpatterns.com/new-and-returning-customers/
regards,
S
Covering 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 |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |