Finding Next Row in Group and Evaluating Against a Set Criteria

[Steve_M32]

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

[Jihwan_Kim · Accepted solution]

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