Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mronti
Regular Visitor

Calculating Inactive Customers

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 ?

5 REPLIES 5
Anonymous
Not applicable

Hi  @mronti ,

I created some data:

vyangliumsft_0-1673853956452.png

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.

vyangliumsft_1-1673853956454.png

3. Result:

X-axis – [Date]

Y axis – [customerID] . Set is Count

vyangliumsft_2-1673853956459.png

 

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

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
Solution Sage
Solution Sage

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I would appreciate that

Data FormatData Format

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.