Calculating Inactive Customers

[mronti]

Hello Everyone,

In my data model I have a sales fact table which contains a customerID and a purchase date. In the case that I am trying to create users who has not made a purchase after their first purchase and who has not made a purchase for 3 or more  years are considered inactive customers. I need a measure that returns the count of these users so that I can create a trend line with time axis. Can you help me please ?

[v-yangliu-msft]

Hi  @mronti ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Flag =
var _mindate=
MINX(FILTER(ALL('Table'),'Table'[cusomerID]=MAX('Table'[cusomerID])),[Date])
var _maxdate=
MINX(FILTER(ALL('Table'),'Table'[cusomerID]=MAX('Table'[cusomerID])&&'Table'[Date]>_mindate),[Date])
var _day=
DATEDIFF(_mindate,_maxdate,DAY)
var _3year=
365 * 3
var _if=
IF(
    _day =BLANK() || _day>_3year,1,0)
return
IF(
    _if=1 &&MAX('Table'[Date]) = _mindate,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

X-axis – [Date]

Y axis – [customerID] . Set is Count

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mronti]

Hello, thank you for yor answer 🙂

I mostly get what you did with the dax but there are some points in the output that doesn't seems right. And I would appreciate if you could help me with that as well. Since the first three years are the limit  there should not be any inactive users yet we still get some inactive user count in the third month of the first year(in the dataset and pbix you provided).  What I want to see for each year is the count of inactive users who hasn't made a purchase in three years prior. For example in 2017 I should see the count of customers who hasn't made a purchase since 2014 or earlier
Thank you for your time 

[bolfri]

Without sample data I can only describe you without any specific DAX code how to do that. I don't know if this suits your needs and if you can implement this solution to your fact_table.

[mronti]

I would appreciate that

[mronti]

Data Format

Data simply looks like this there are reocurring purchases with different dates but from the same customer.