Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Formula to find specific orders

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!

Need Help

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

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

     

     

    please correct that.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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's avatar
    Anonymous
    Not applicable
    5 years ago

    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