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.
Hi all,
I am trying to create a measure that shows the number of days between:
A) The MAX transaction date that each customer buys a specific product. This product is selected by a filter. And
B) Each subsequent transaction made by that customer, for every single product (not just the filtered item).
In the below example, Product A has been selected using the filter.
Is this possible using DAX?
Solved! Go to Solution.
Hi @chuckle,
Please check following steps and see if the result achieve your expectation:
1. Create calculated table as slicer:
Table 2 = DISTINCT('Table'[product])
2. Create measure:
Measure =
var sv = SELECTEDVALUE('Table 2'[product])
var a = CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[customer] = MAX('Table'[customer])&&'Table'[product] = sv))
var b = CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[customer] = MAX('Table'[customer])&&'Table'[product] = MAX('Table'[product])))
return
IF(NOT(ISFILTERED('Table 2'[product])),"",IF(MAX('Table'[product]) = sv,"",IF(MAX('Table'[date])=b,DATEDIFF(a,MAX('Table'[date]),DAY),"")))
3. Result would be shown as below:
BTW, Pbix as attached, hopefully works for you.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @chuckle,
Please check following steps and see if the result achieve your expectation:
1. Create calculated table as slicer:
Table 2 = DISTINCT('Table'[product])
2. Create measure:
Measure =
var sv = SELECTEDVALUE('Table 2'[product])
var a = CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[customer] = MAX('Table'[customer])&&'Table'[product] = sv))
var b = CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[customer] = MAX('Table'[customer])&&'Table'[product] = MAX('Table'[product])))
return
IF(NOT(ISFILTERED('Table 2'[product])),"",IF(MAX('Table'[product]) = sv,"",IF(MAX('Table'[date])=b,DATEDIFF(a,MAX('Table'[date]),DAY),"")))
3. Result would be shown as below:
BTW, Pbix as attached, hopefully works for you.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |