How to count a later order of a specific product type and within a certain time frame

[MarcLykke]

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

[johnt75]

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" )

[Greg_Deckler · Accepted 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

[MarcLykke]

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? 🙂