Formula to find specific orders

[Anonymous]

Hi!

Hope someone are able to help med with a solution for below - would be very appreciated. 

All in all, I have two types of orders connected to my data, "ht_order" and "order". What I need to figure out is how many of my "orders" that are created after the same customer placed a "ht_order", this given certain rules. 

Step by step my questions:

1) Last order date - HT -->for each customer(email) (based on order_type = ht_order)
2) Last order date - Order -->for each customer(email) (based on order_type = order and where product_category = B)
3) The number of days between above order dates --> Last order date (Order) - Last order date (HT)
4) Calculated measure (or column?) with orders where order_type = order and product category = B and order_date >= Last order date HT and Dates between Last order date HT and Last order date Order <=30days
IF above is correct --> "HT" ELSE "Not HT"
5) From number 4 understand which orders that are "HT" and calculate the sales

Expected outcome: 
Number of HT-order = 6 
Sales = 790

(Please, see details in attachments)

All data provided through this link (Google Drive): https://drive.google.com/drive/folders/17hi4N_CAPjzqZlpqaNbgbQm5ptHGJcLP?usp=sharing

Let me know if any questions - big thanks in advance!

[Anonymous]

HI @Anonymous,

According to your description, I think you can try to use the below formula to find out the previous dates and calculate.

Measure =
VAR currDate =
    MAX ( Table[Date] )
VAR lastHT =
    CALCULATE (
        MAX ( Table[Date] ),
        FILTER ( ALLSELECTED ( Table ), [Date] < currDate && [order_type] = "ht_order" ),
        VALUES ( Table[Email] )
    )
VAR lastOrder =
    CALCULATE (
        MAX ( Table[Date] ),
        FILTER ( ALLSELECTED ( Table ), [Date] < currDate && [order_type] = "order" ),
        Product[product_category] = "B",
        VALUES ( Table[Email] )
    )
VAR diff =
    ABS ( DATEDIFF ( lastHT, lastOrder, DAY ) )
RETURN
    IF ( diff <= 30, "HT", "Not HT" )

BTW, your sample data seems to contain duplicate records, how did you handle these records? (they may effect the formula usage)
Regards,

Xiaoxin Sheng

[lbendlin]

Your Date table is not contiguous an it has duplicate values:

please correct that.

[Anonymous]

Hi @lbendlin and thanks for noticing the duplicates, 

The data is now reloaded and found under the same link as before. 

Thanks for helping!

[Anonymous]

HI @Anonymous,

I modify the measure formula based on your table fields, did this formula works on your side?

Measure =
VAR currDate =
    MAX ( 'DATE'[date_key] )
VAR lastHT =
    CALCULATE (
        MAX ( SALES[date_key] ),
        FILTER (
            ALLSELECTED ( SALES ),
            [date_key] < currDate
                && [order_type] = "ht_order"
        ),
        VALUES ( SALES[customer_key] )
    )
VAR productList =
    CALCULATETABLE (
        VALUES ( 'PRODUCT'[product_key] ),
        FILTER ( ALLSELECTED ( 'PRODUCT' ), [product_category] = "B" )
    )
VAR lastOrder =
    CALCULATE (
        MAX ( SALES[date_key] ),
        FILTER (
            ALLSELECTED ( SALES ),
            [date_key] < currDate
                && [order_type] = "order"
                && [product_key] IN productList
        ),
        VALUES ( SALES[customer_key] )
    )
VAR diff =
    ABS (
        DATEDIFF (
            LOOKUPVALUE ( 'DATE'[full_date], 'DATE'[date_key], lastHT ),
            LOOKUPVALUE ( 'DATE'[full_date], 'DATE'[date_key], lastOrder ),
            DAY
        )
    )
RETURN
    IF ( diff <= 30, "HT", "Not HT" )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous and thanks for the updated formula. Unfortunately I'm getting "Not HT" for all orders so something seems off.. 

Hope you have a good understanding for why. 

Thanks!

[Anonymous]

Hi @Anonymous,

My formula is based on current row contents, have you add/remove any other category fields that may affect the current row content aggregations?
If they change on the row contents summary, then the formula will calculate on the wrong aggregations and get exceptional results.

Regards,

Xiaoxin Sheng