Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.