Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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 ?
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
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
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.
Proud to be a Super User!
I would appreciate that
Data Format
Data simply looks like this there are reocurring purchases with different dates but from the same customer.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
60 | |
58 | |
54 | |
36 | |
33 |
User | Count |
---|---|
79 | |
66 | |
45 | |
45 | |
43 |