Count Latest Date in an Aggregated table of purchase orders based with Unique shipments - Question

[TylerVono]

Hello, I am trying to count just the latest date of a column in a aggregated pool of purchase orders. Every day we take a snapshot of the purchase order's current conditions, at time the buyers change the promised date based on the suppliers best date.

I'd like to record the current days 'Past Due', however there are multiple changes of the date column which compounds the data, makingit pile up and unable to calculate distinct unique shipment dates.

Chat GPT doesnt seem to help me... wondering if the Power BI community has any good workarounds. Keep in mind I am doing a DirectQuery from a coworkers database so Date/Time control will not be in my favor.

Here is an example, i.e. PO# 1144900, Line 2 and Shipment 1 is repeated 4 times, technically I just want to record it as 1 single occurance as a past due. You can see the date change, but I simple want it to be at its LATEST date.

Thoughts, Ideas? I appreciate any advice here.

[Ashish_Mathur]

Hi,

Share sample data of a few PO's in a format that can be pasted in an MS Excel file and show the expected result.

[TylerVono]

Hey Ashish, Can you let me know if you've got any help or suggestions for me? Thanks for looking into this!

[Ashish_Mathur]

Hi,

I have not understood your requirement.  For the table that you have shared, show the expected result.

[TylerVono]

Thanks for your patience Ashish, Ok so here is a example below:

This purchase order is repeated twice, the only difference is the date column called the "First Promised Date". I would like to just count the latest date a single time, currently this purchase order is being counted twice due to my inaccurate measure. I just want to record it as one single occurance . You can see the date change, but I simple want it to be at its LATEST date visible, i.e. 11/20/2023 MAX not 6/19/2023.

Supplier	PO Number	Item Number	Line	Shipment	Product Line	Unique Shipment	First Promise Date
KIRSH FOUNDRY INC	1141140	S-90988 RAW	1	1	PMG	114114011	6/19/2023 0:00
KIRSH FOUNDRY INC	1141140	S-90988 RAW	1	1	PMG	114114011	11/20/2023 0:00

[Ashish_Mathur]

I'll try once again - "For the table that you have shared, show the expected result"

[TylerVono]

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Supplier", type text}, {"PO#", Int64.Type}, {"Item #", type text}, {"Line", Int64.Type}, {"Shipment", Int64.Type}, {"Product Line", type text}, {"Unique shipment", Int64.Type}, {"First promise date", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Supplier", "PO#", "Item #", "Line", "Shipment", "Product Line", "Unique shipment"}, {{"Count", each Table.Max(_,"First promise date")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"First promise date"}, {"First promise date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"First promise date", type date}})
in
    #"Changed Type1"

Hope this helps.

[TylerVono]

Hey Ashish, So the problem is I cannot access the Power Query Editor since I am pulling this table via DirectQuery. Do you know any measures or custom column that can be used as a workaround?

[TylerVono]

Hello Ashish - Thank you so much for your help, here is an example ... I appreciate any help you can provide.

Supplier	Count of Buyer	PO Number	Item Number	Line	Shipment	Product Line	Unique Shipment	First Promise Date
SIGNICAST CORP	1	1127031	FD-4330	1	1	VACUFUSE II	112703111	4/3/2023 0:00
SIGNICAST CORP	1	1127036	FD-4334	1	1	VACUFUSE II	112703611	6/2/2023 0:00
SIGNICAST CORP	1	1127038	FD-4329	1	1	VACUFUSE II	112703811	6/13/2013 0:00
SIGNICAST CORP	1	1129908	SD-6169	1	1	N/A	112990811	6/26/2023 0:00
APLICACIONES INDUSTRIALES DE CALIDAD SA DE CV	1	1136111	F-10264	1	1	HOLDERS	113611111	3/9/2023 0:00
APLICACIONES INDUSTRIALES DE CALIDAD SA DE CV	1	1136111	F-10264	1	1	HOLDERS	113611111	5/2/2023 0:00
ASCHER BROTHERS	1	1136645	QG-1590233	1	1	N/A	113664511	5/5/2023 0:00
KIRSH FOUNDRY INC	1	1141140	S-90988 RAW	1	1	PMG	114114011	6/19/2023 0:00
KIRSH FOUNDRY INC	1	1141140	S-90988 RAW	1	1	PMG	114114011	11/20/2023 0:00
NAMEPLATE & PANEL TECHNOLOGY	1	1144900	G-9615	2	1	N/A	114490021	8/3/2023 0:00
NAMEPLATE & PANEL TECHNOLOGY	1	1144900	G-9615	2	1	N/A	114490021	8/8/2023 0:00
NAMEPLATE & PANEL TECHNOLOGY	1	1144900	G-9615	2	1	N/A	114490021	8/15/2023 0:00
NAMEPLATE & PANEL TECHNOLOGY	1	1144900	G-9615	2	1	N/A	114490021	8/17/2023 0:00
H3 MANUFACTURING GROUP LLC	1	1146571	CA-240	1	1	PMG	114657111	10/31/2022 0:00
PRECISION ENTERPRISES INC	1	1146811	SD-6790	1	1	N/A	114681111	7/19/2023 0:00
NEP ELECTRONICS INC	1	1147295	FD-3411	3	1	TRIPSAVER II	114729531	11/1/2023 0:00
NEP ELECTRONICS INC	1	1147295	FD-3411	3	1	TRIPSAVER II	114729531	12/19/2023 0:00
NEP ELECTRONICS INC	1	1147295	FD-3411	3	1	TRIPSAVER II	114729531	1/11/2024 0:00
BISON GEAR AND ENGINEERING CO	1	1147416	SD-5785	1	4	PMG	114741614	11/6/2023 0:00
BISON GEAR AND ENGINEERING CO	1	1147416	SD-5785	1	4	PMG	114741614	11/14/2023 0:00
BISON GEAR AND ENGINEERING CO	1	1147416	SD-5785	1	4	PMG	114741614	1/8/2024 0:00
BISON GEAR AND ENGINEERING CO	1	1147416	SD-5785	1	5	PMG	114741615	11/29/2023 0:00
BISON GEAR AND ENGINEERING CO	1	1147416	SD-5785	1	5	PMG	114741615	12/11/2023 0:00
BISON GEAR AND ENGINEERING CO	1	1147416	SD-5785	1	5	PMG	114741615	1/26/2024 0:00