Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a measure like below wich returns me all active customers whose in last 90 days bought product category = 'A'
CustomersActive last 90 days:= VAR Last90Days= MAX (DimDate[Date) - 90 RETURN CALCULATE ( DISTINCTCOUNT ( FactSales[CustomerKey]); DimProduct[Category] = "A"; FILTER ( ALL (DimDate[Date); DimDate[Date] >= Last90Day && DimDate[Date] <= MAX (DimDate[Date]) ) )
Now I need to create a measure INACTIVE CUSTOMERS:
Rule:
1 - Make a list of all distinct customers whose someday bought product = "B" and the date of this was less than my actual filter date
2 - Create a measure using also a distinct count of the last 90 days, but comparing the first list to my actual
3 - If custumer was in the first list (product B) and in my last 90 days (product A) is not appearing, then 1, else 0
Thanks
I have update my solution, could you have a look at it?
Best Regards
Maggie
Hi @v-juanli-msft, I marked your answer as a solution.
Could you help with this one? Is kind of different
Do you have sample data for this?
@Greg_Deckler
Try this one:
FactSales | |||
KeyDate | KeyCustomer | KeyProduct | Total |
1 | 1 | 1 | 12,9 |
1 | 2 | 2 | 13 |
1 | 3 | 1 | 156,4 |
1 | 4 | 1 | 564,8 |
2 | 1 | 1 | 894,8 |
2 | 2 | 1 | 56,5 |
3 | 1 | 2 | 564,85 |
3 | 2 | 3 | 564,8 |
4 | 1 | 1 | 1325,6 |
4 | 2 | 1 | 132,3 |
Customer | |
KeyCustomer | Name |
1 | Jean |
2 | Mari |
3 | Lisa |
4 | Julian |
5 | Jhonny |
Calendar | |
KeyDate | Date |
1 | 01/01/2018 |
2 | 02/01/2018 |
3 | 01/05/2018 |
4 | 01/08/2018 |
Product | |
KeyProduct | Product |
1 | A |
2 | B |
3 | C |
I'd like to see something like:
Customer | Inactive |
Blabla | 1 |
abcd | 0 |
ggggg | 1 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |