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 everyone
I am trying to determine whether if a person's previous order is followed by another (specific product type of) order in the future.
In the example below I want to determine whether a previous order is followed by another order of the product type B. Futhermore, the following order of the product type B must be placed within 31 days from the previous order to be valued with a "Yes".
Can anyone help me how to write the DAX to create the following new column? ("Later order B +31 days")
Order ID | Person ID | Order date | Product type | Later order B +31 days |
1 | 1 | 29/11 2021 | A | Yes |
2 | 1 | 05/12 2021 | B | No |
3 | 1 | 10/12 2021 | C | No |
4 | 1 | 06/03 2022 | B | No |
Solved! Go to Solution.
@MarcLykke Try something like:
Column =
VAR __CurrentDate = [Order date]
VAR __Customer = [Person ID]
VAR __NextOrderDate = MINX(FILTER('Table',[Person ID] = __Customer && [Order date] > __CurrentDate),[Order date])
VAR __NextProduct = MINX(FILTER('Table',[Person ID] = __Customer && [Order date] > __NextOrderDate),[Product type])
RETURN
IF(__NextProduct = "B" && (__NextOrderDate - __CurrentDate) * 1. <= 31, "Yes", "No")
The basic pattern is taken from MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
Later order B + 31 Days =
var currentCustomer = 'Table'[Person ID]
var currentDate = 'Table'[Order date]
return IF( ISEMPTY( FILTER( ALL('Table'), 'Table'[Person ID] = currentCustomer &&
'Table'[Order date] <= currentDate + 31 ) ), "No", "Yes" )
@MarcLykke Try something like:
Column =
VAR __CurrentDate = [Order date]
VAR __Customer = [Person ID]
VAR __NextOrderDate = MINX(FILTER('Table',[Person ID] = __Customer && [Order date] > __CurrentDate),[Order date])
VAR __NextProduct = MINX(FILTER('Table',[Person ID] = __Customer && [Order date] > __NextOrderDate),[Product type])
RETURN
IF(__NextProduct = "B" && (__NextOrderDate - __CurrentDate) * 1. <= 31, "Yes", "No")
The basic pattern is taken from MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
Thank you very much Greg!
I changed the variable for Next product to be similar to the variable for Next order date so they both need to have an order date larger than the Currentdate. But maybe there is a deeper meaning behind you using Order date > NextOrderDate in the variable for Next product? 🙂
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 |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |