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 need a dax measure wich shows me which customer bought product B and C in last 90 days
And another one wich shows me those whose bought product B and C in last 90 days
(based in my filter date context)
Below is like it should be:
Can someone help me?
Here is a sample data if needed:
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 |
Solved! Go to Solution.
I made some change to my previous solution.
Still add the "date" column from my created date table to the slicer, but set the slicer as "list" mode.
Then create a measure in the date table
Measure = DATEADD('Table'[Date],-90,DAY)
when you select "2018/8/1" in the slicer, the measure would show the 90 days before the "2018/8/1".
Then modify the measure bought last 90 days as below
bought last 90 days = IF(MAX([date])>=[Measure]&&MAX([date])<=MAX('Table'[Date]),1,0)
Best Regards
Maggie
1.Create a date table
Table = CALENDAR(DATE(2018,1,1),DATE(2018,12,31)) according to the dataset, DATE(2018,1,1) should be the min date, DATE(2018,12,31) should be the max date. You could modify them for your real dataset.
2.Create relationship between the date table and Calendar table
3. add the "date" column to a slicer, and select "relative" ->"Last","90",'Days"
4. Create a calculated column in the FactSales table
date = RELATED('Calendar'[Date])
5. Then create measures
bought last 90 days = IF(MAX([date])>=MIN('Table'[Date]),1,0) bought B,C = IF(MAX([KeyProduct])=2||MAX([KeyProduct])=3,1,0)
if bought = IF([bought B,C]=1&&[bought last 90 days]=1,1,0)
count bought = CALCULATE(COUNT(Customer[Name]),FILTER(ALL(Customer),[if bought]=1))
count not bought = CALCULATE(COUNT(Customer[Name]),FILTER(ALL(Customer),[if bought]=0))
Best Regards
Maggie
@v-juanli-msft
I can't use this case.
When the user filter a date, example: 01/06/2018
The measure must look 90 days back and check in fact table if that custuomer appeared and bought product B or C, then 1 if yes and 0 if doesn't
I did something like this;
CALCULATE (
DISTINCTCOUNT(
Customer[CustomerKey);
DimProduct[Product] in {"A"; "B"}
FILTER(DimDate; DimDate[Date] >= MAX(DimDate[Date])-90 && DimDate[Date] <> MAX(DimDate[Date]))
)
But the problem is being looking back, it is not taking 90 days before the filter and is calculating wrong
I made some change to my previous solution.
Still add the "date" column from my created date table to the slicer, but set the slicer as "list" mode.
Then create a measure in the date table
Measure = DATEADD('Table'[Date],-90,DAY)
when you select "2018/8/1" in the slicer, the measure would show the 90 days before the "2018/8/1".
Then modify the measure bought last 90 days as below
bought last 90 days = IF(MAX([date])>=[Measure]&&MAX([date])<=MAX('Table'[Date]),1,0)
Best Regards
Maggie
I do not know how to use dax measure. I would solve it in this way. You can choose the time and then click what you are interested in.
Thanks @Anonymous, but I'll really need a measure like that
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |