Forum Discussion
Helper IFind date with several filters
Dear All,
Hope you can help me :
we are the 29/11 and we need to find if there is a new delivery date for the previous extraction items
| EXTRACTION DATE | ORDER | ITEM ORDER | DT_DELIVERY | DT_NEW_DELIVERY |
| 24/11/2017 | S440 | 10 | 05/12/2017 | 10/12/2017 |
| 28/11/2017 | S440 | 10 | 05/12/2017 | 10/12/2017 |
| 29/11/2017 | S440 | 10 | 10/12/2017 |
I try this formula but I got the last delivery of the table (when I use max) or the first delivery (when I use MIN)
=CALCULATE( MAX(T_DATA_OPEN[DT_DELIVERY]) ;FILTER(T_DATA_OPEN;T_DATA_OPEN[ORDER]=[ORDER]) ;FILTER(T_DATA_OPEN;T_DATA_OPEN[ITEM_ORDER]=[ITEM_ORDER]) ;FILTER(T_DATA_OPEN;T_DATA_OPEN[DT_DELIVERY]>EARLIER([DT_DELIVERY])) ;FILTER(T_DATA_OPEN;T_DATA_OPEN[EXTRACTION_DATE]> EARLIER ([EXTRACTION_DATE]))
Thanks for your help
novicenovice,
You can get expected result by creating a simple measure.
Measure = MAX(T_DATA_OPEN[DT_DELIVERY])
If you need to create a calculted column, use DAX below.
Column = VAR ORDERid = T_DATA_OPEN[ORDER] VAR ITEMID=T_DATA_OPEN[ITEM ORDER] RETURN CALCULATE(MAX(T_DATA_OPEN[DT_DELIVERY]),FILTER(T_DATA_OPEN,T_DATA_OPEN[ORDER]=ORDERid && T_DATA_OPEN[ITEM ORDER]= ITEMID))
Regards,
Lydia
5 Replies
- parry2k
Super User
I guess you are looking most recent delivery date for an order, correct?
novicenoviceYes, on orders and the item order
novicenovice,
You can get expected result by creating a simple measure.
Measure = MAX(T_DATA_OPEN[DT_DELIVERY])
If you need to create a calculted column, use DAX below.
Column = VAR ORDERid = T_DATA_OPEN[ORDER] VAR ITEMID=T_DATA_OPEN[ITEM ORDER] RETURN CALCULATE(MAX(T_DATA_OPEN[DT_DELIVERY]),FILTER(T_DATA_OPEN,T_DATA_OPEN[ORDER]=ORDERid && T_DATA_OPEN[ITEM ORDER]= ITEMID))
Regards,
Lydia- novicenovice
Hello Lydia,
Thanks it works :)
Regards.
novice
