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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
novicenovice
Helper I
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 DATEORDERITEM ORDERDT_DELIVERYDT_NEW_DELIVERY
24/11/2017S4401005/12/201710/12/2017
28/11/2017S4401005/12/201710/12/2017
29/11/2017S4401010/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
v-yuezhe-msft
Employee
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))

1.JPG


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.

View solution in original post

5 REPLIES 5
v-yuezhe-msft
Employee
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))

1.JPG


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.

Hello Lydia,

 

Thanks it works 🙂

 

Regards.

 

novice

parry2k
Super User
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.

Yes, on orders and the item order

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.