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!

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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