cancel
Showing results for
Did you mean:
New Member

## Finding Next Row in Group and Evaluating Against a Set Criteria

Hello,

I need help with a DAX expression, or M forumla I guess, to find orders placed by the same customer for the same thing in close proximity to one another.

I want to be able to group, further transform and visualise rows which contain an order from a customer who has ordered the same product within a period of 30 days of another similar order.

From the example data you can see customer 1 orders eggs 4 times. The egg orders in rows 1, 4 and 6 are all placed within 30 days of another order so I want to populate my new column with a "Yes". The egg order in row 9 isn't within 30 days of another order, so that gets a "No" in the new column.

Similarly customer 3 places 2 carrot orders within 30 days of each other, so the orders in rows 4 and 8 also get a "Yes" in the new column.

I'm sure I need to use the EALIER function somehow, but I can't figure how to express it.

Any help greatly appreciated!

Example Data Table

 Row Order Date Customer UID Product Quantity Ordered New Column Placed within 30 days of another order 1 01/05/2022 1 Eggs 10 Yes 2 01/10/2022 1 Carrots 5 No 3 01/15/2022 2 Tomatoes 1 No 4 01/16/2022 1 Eggs 15 Yes 5 01/21/2022 3 Carrots 5 Yes 6 02/04/2022 1 Eggs 12 Yes 7 02/04/2022 2 Carrots 10 No 8 02/15/2022 3 Carrots 4 Yes 9 03/16/2022 1 Eggs 21 No
1 ACCEPTED SOLUTION
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new column.

``````Placed within 30 days of another order CC =
VAR _currentorderdate = 'Table'[Order Date]
VAR _nextorderdate =
MINX (
FILTER (
'Table',
'Table'[Customer UID] = EARLIER ( 'Table'[Customer UID] )
&& 'Table'[Product] = EARLIER ( 'Table'[Product] )
&& 'Table'[Order Date] > EARLIER ( 'Table'[Order Date] )
),
'Table'[Order Date]
)
VAR _prevorderdate =
MAXX (
FILTER (
'Table',
'Table'[Customer UID] = EARLIER ( 'Table'[Customer UID] )
&& 'Table'[Product] = EARLIER ( 'Table'[Product] )
&& 'Table'[Order Date] < EARLIER ( 'Table'[Order Date] )
),
'Table'[Order Date]
)
RETURN
SWITCH (
TRUE (),
ISBLANK ( _prevorderdate ) && ISBLANK ( _nextorderdate ), "No",
NOT ISBLANK ( _nextorderdate )
&& DATEDIFF ( _currentorderdate, _nextorderdate, DAY ) < 30, "Yes",
NOT ISBLANK ( _prevorderdate )
&& DATEDIFF ( _prevorderdate, _currentorderdate, DAY ) < 30, "Yes",
"No"
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new column.

``````Placed within 30 days of another order CC =
VAR _currentorderdate = 'Table'[Order Date]
VAR _nextorderdate =
MINX (
FILTER (
'Table',
'Table'[Customer UID] = EARLIER ( 'Table'[Customer UID] )
&& 'Table'[Product] = EARLIER ( 'Table'[Product] )
&& 'Table'[Order Date] > EARLIER ( 'Table'[Order Date] )
),
'Table'[Order Date]
)
VAR _prevorderdate =
MAXX (
FILTER (
'Table',
'Table'[Customer UID] = EARLIER ( 'Table'[Customer UID] )
&& 'Table'[Product] = EARLIER ( 'Table'[Product] )
&& 'Table'[Order Date] < EARLIER ( 'Table'[Order Date] )
),
'Table'[Order Date]
)
RETURN
SWITCH (
TRUE (),
ISBLANK ( _prevorderdate ) && ISBLANK ( _nextorderdate ), "No",
NOT ISBLANK ( _nextorderdate )
&& DATEDIFF ( _currentorderdate, _nextorderdate, DAY ) < 30, "Yes",
NOT ISBLANK ( _prevorderdate )
&& DATEDIFF ( _prevorderdate, _currentorderdate, DAY ) < 30, "Yes",
"No"
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.