cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Steve_M32
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

 

RowOrder DateCustomer UIDProductQuantity OrderedNew Column Placed within 30 days of another order
101/05/20221Eggs10Yes
201/10/20221Carrots5No
301/15/20222Tomatoes1No
401/16/20221Eggs15Yes
501/21/20223Carrots5Yes
602/04/20221Eggs12Yes
702/04/20222Carrots10No
802/15/20223Carrots4Yes
903/16/20221Eggs21No
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
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.

 

Jihwan_Kim_0-1669729182543.png

 

 

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.


Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
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.

 

Jihwan_Kim_0-1669729182543.png

 

 

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.


Go to My LinkedIn Page


Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors