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
lbendlin's avatar
lbendlin
Icon for Super User rankSuper 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 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!