Need Help with Filtering to Max Date

[Samcooke]

Hi all, I am struggling with something that I thought would be easy but apparently not for me. I have a table of orders that have the product and the ship date for easch product. Some of the orders have multiple ship dates for the same Product Name. I am looking to filter to just display the latest (Max) Ship_Created_Date for each Product Name for each order. So in the below example it would only show the highlighted records for that particular order. Is there a DAX query, or some other solution, that can help with this?

[parry2k]

@Samcooke ok, good luck but it is unnecessary, anyhow, you have the solution. Enjoy!

[Samcooke]

Thanks for your feedback. I ended up using the below query I found somewhere else and replaced the table and fields with mine. It worked. I had never used "Filter" in a query before

MaxOfMonth = IF(
	Table1[date] = CALCULATE(
		MAX(TableName[date]),
		FILTER(
			ALL(TableName),
			MONTH(TableName[date]) = MONTH(EARLIER(TableName[date])) &&
			YEAR(TableName[date]) = YEAR(EARLIER(TableName[date]))
		)
	),
	TRUE,
	FALSE
)

[Ashish_Mathur]

Hi,

Why have you n ot highlighted th e Product B row?  Also, do you want a measure or a Power Query solution?  Share data in a format that can be pasted in an MS Excel file.

[parry2k]

@Samcooke why not add a measure for maximum ship date and then use that in the visual instead of ship_created_Date

Max Ship Date = MAX ( Table[Ship_Created_Date] )

[Samcooke · Accepted solution]

Thanks for your feedback. I ended up using the below query I found somewhere else and replaced the table and fields with mine. It worked. I had never used "Filter" in a query before

MaxOfMonth = IF(
	Table1[date] = CALCULATE(
		MAX(TableName[date]),
		FILTER(
			ALL(TableName),
			MONTH(TableName[date]) = MONTH(EARLIER(TableName[date])) &&
			YEAR(TableName[date]) = YEAR(EARLIER(TableName[date]))
		)
	),
	TRUE,
	FALSE
)