Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
User | Count |
---|---|
75 | |
74 | |
44 | |
31 | |
27 |
User | Count |
---|---|
99 | |
89 | |
52 | |
48 | |
46 |