Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Find 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

1 ACCEPTED SOLUTION
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

Hello Lydia,

Thanks it works 🙂

Regards.

novice

Super User

I guess you are looking most recent delivery date for an order, correct?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper I
Yes, on orders and the item order
Super User

posted a solution an hour ago, modify it as you see fit

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors