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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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