DAX to find Customer count who order exact same order list more than once in bimonthly span

[Anonymous]

Hi Team,

I need to create a DAX but not getting any logic on how to build this one. I extend my hand to my Power BI family for help. I am able to pull the customers who placed more than one order but not able to check if the items bought in an two orders are same items and quantities.

KPIs :

• On a bi-monthly basis share of customers having more than 1 consecutive order composed by the same variants (with same qtys) 

• Compare the above to the share of customers having more than 1 consecutive order composed by different variants (within orders)

Tables

Order Table (PK - Order No)

Customer	Order no	Order Date
Customer A	123	7/1/2022
Customer A	321	7/23/2022
Customer A	231	8/12/2022
Customer A	1981	8/18/2022

Order Items (PK No - Order No)

Order No	Order Items	Order Quantity
123	Matrix Serum 250 ml	1
123	Sunsilk Shampoo 340 ml	1
123	Sunsilk Conditioner 110 ml	2
321	Matrix Serum 250 ml	1
321	Pantene Shampoo 340 ml	1
231	Matrix Serum 250 ml	1
231	Sunsilk Shampoo 340 ml	1
231	Sunsilk Conditioner 110 ml	2
1981	Matrix Serum 250 ml	1
1981	Sunsilk Shampoo 340 ml	1
1981	Sunsilk Conditioner 110 ml	2

Expected Result: DAX output should provide Customer A (Count 1) because Customer A has ordered exact same order twice on 7/1/2022 and 8/12/2022

Please let me know if any further explanation is needed. Any help will be highly appreciable. 

[AlexisOlson · Accepted solution]

This is not super simple so let's start without considering dates.

You can find customers with multiple identical orders by creating a summary of customers and orders and converting the list of order items and quantities into a single string for each order. Once you have this, you can group on customers and string to see which combinations are duplicated (have a count > 1).

Here's my attempt at implementing this:

Customers with Repeat Orders =
VAR Summary =
    SUMMARIZE (
        OrderItems,
        Orders[Customer],
        OrderItems[Order No],
        "ItemString",
            CONCATENATEX (
                OrderItems,
                OrderItems[Order Items] & ": " & OrderItems[Order Quantity],
                "; ",
                OrderItems[Order Items]
            )
    )
VAR Grouped =
    GROUPBY (
        Summary,
        Orders[Customer],
        [ItemString],
        "Count", SUMX ( CURRENTGROUP (), 1 )
    )
VAR Repeats = SUMMARIZE ( FILTER ( Grouped, [Count] > 1 ), Orders[Customer] )
RETURN
    COUNTROWS ( Repeats )

To visualize this, here's what a couple of the intermediate tables look like:

Summary:

Grouped: