First Record based on the Date

[alisag09]

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

[Anonymous · Accepted solution]

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 _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alisag09]

Many thanks Polly....it worked.

[alisag09]

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?

[Anonymous]

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 _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

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 _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alisag09]

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