Forum Discussion
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...
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
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!
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