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 fig...
Need Help
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