Forum Discussion
First Record based on the Date
Hello All,
I have to Tables, Products and PurchaseLines and relation is between these 2 is ItemID. To print the earliest Receipt Possible Date for a ItemID, i can use Earliest but how can i get the corresposnding Purchase OrderNo for that record in PurchaseLines Table, similar to below,
| ItemID | Earliest Receipt Date | PurchaseID |
| ABCDEF | 20/02/2023 | PO0000023 |
| XYZEFG | 12/03/2023 | PO0000086 |
for ABCDEF ItemID i have 4 PO lines with 4 differnet PO# and dates.
| ITEMID | PurchID | ReceiptDate |
| ABCDEF | PO0000019 | 28/02/2023 |
| ABCDEF | PO0000023 | 20/02/2023 |
| ABCDEF | PO0000038 | 08/03/2023 |
| ABCDEF | PO0000056 | 17/04/2024 |
Hi alisag09 ,
Please refer to my pbix file.
Create measures.
date_ = CALCULATE(MIN(purchase[DeliveryDate]),FILTER(ALL(purchase),purchase[ItemId]=SELECTEDVALUE(products[ItemNumber])))Description = "Description of -" &" "&MAX(products[ItemNumber])Earliest PurchdID = CALCULATE(MAX(purchase[PurchID]),FILTER(ALL(purchase),purchase[ItemId]=SELECTEDVALUE(products[ItemNumber])))If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ PollyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi alisag09 ,
Please refer to my pbix file.
Create measures.
date_ = CALCULATE(MIN(purchase[DeliveryDate]),FILTER(ALL(purchase),purchase[ItemId]=SELECTEDVALUE(products[ItemNumber])))Description = "Description of -" &" "&MAX(products[ItemNumber])Earliest PurchdID = CALCULATE(MAX(purchase[PurchID]),FILTER(ALL(purchase),purchase[ItemId]=SELECTEDVALUE(products[ItemNumber])))If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ PollyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Many thanks Polly....it worked.
Hi alisag09 ,
Please have a try.
Create a measure.
measure = MINX ( FILTER ( ALL ( table ), table[item id] = SELECTEDVALUE ( table[item id] ) ), table[receiptdate] )Or a column.
column = MINX ( FILTER ( ( table ), table[item id] = EARLIER ( table[item id] ) ), table[receiptdate] )How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ PollyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello, Following are 2 Tables Namely Products & Purchase
Products,
ItemNumber 0110322 0110323 0110328 0110334 0110401 0110493 0230116 Purchase
PurchID ItemId DeliveryDate PO-000730 0110322 2/6/2023 PO-000730 0110322 3/6/2023 PO-000730 0110322 4/6/2023 PO-000730 0110323 4/24/2023 PO-000730 0110323 4/24/2023 PO-000730 0110323 4/24/2023 PO-000248 0110328 1/27/2023 PO-000730 0110334 2/6/2023 PO-000730 0110334 3/6/2023 PO-000730 0110334 4/6/2023 PO-000730 0110401 2/6/2023 PO-000730 0110401 3/6/2023 PO-000730 0110493 2/6/2023 PO-000730 0110493 3/6/2023 PO-000730 0110493 4/6/2023 Result
ItemNumber Description Earliest Date Earliest PurchdID 0110322 Description of - 0110322 2/6/2023 PO-000730 0110323 Description of - 0110323 4/24/2023 PO-000730 0110328 Description of - 0110328 1/27/2023 PO-000248 0110334 Description of - 0110334 2/6/2023 PO-000730 0110401 Description of - 0110401 2/6/2023 PO-000730 0110493 Description of - 0110493 2/6/2023 PO-000715 0230116 Description of - 0230116 0230135 Description of - 0230135 0230158 Description of - 0230158 0240107 Description of - 0240107 0240108 Description of - 0240108
Hello,
Thanks Polly, but May i kNow which Table is what? Can you please rewrite the Query Using Table Names as
ItemTable & PurchTable at least for Column Query?
Hi alisag09 ,
It seems you have 2 tables. Could you please provide a screenshot with your desired output?
You can provide some data like you have provided.
And the desired output like the following, let me know you want.
Best Regards
Community Support Team _ PollyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
