Hi,
I wish to do a simple distinct count of lapsed customers. We define lapsed customers as those who have not made a purchase in the last 3 years, so I have created an explicit measure Lost Days Limit = 1095 + 1.
How do I write the formula to count distinct [CustomerKey] or customers whose [LastTransactionDate] >= [LostDaysLimit]? Thank you.
Hi Linda,
Try
=CALCULATE(
DISTINCTCOUNT( FactTable[CustomerKey] ) ,
[LastTransactionDate] >= [LostDaysLimit] )
Mynda
Hi Mynda,
Thanks. The syntax for [LostDaysLimit] wasn't correct so I ended up typing in 1096 days into the measure.
I then tried the formula you suggested in a couple of ways. One, where the data type of [LastTransactionDate] was a date. Two, where I transformed [LastTransactionDate] into a duration in days. However, both of them returned the full customer count instead of the lapsed customer count.
What else could I try? Thanks
Hi Linda,
My suggested formula was just that, a suggestion based on what you already knew, but in hindsight I should have noticed that you were trying to compare a date to a number i.e. last transaction date >= number of lost days, as that doesn't make sense.
Your lost days limit formula needs to return a date. e.g.
=CALCULATE(
DISTINCTCOUNT( FactTable[CustomerKey] ) ,
[LastTransactionDate] <= DATEADD(DateTable[DateColumn], -1096, days) )
This requires a date in the PivotTable row, column or filters for the DATEADD function to know what date it's shifting -1096 days from.
Mynda
Thank you Mynda. I need to swot up on the time intelligence lessons to get a better handle on this!
