Forum Discussion

Samcooke's avatar
Samcooke
Frequent Visitor
2 years ago
Solved

Need Help with Filtering to Max Date

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?

 

 

 

Need Help
  • Samcooke's avatar
    Samcooke
    2 years ago

    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
)

     

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    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's avatar
      Samcooke
      Frequent Visitor
      2 years ago

      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's avatar
    Ashish_Mathur
    Super User
    2 years ago

    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.

  • Samcooke's avatar
    Samcooke
    Frequent Visitor
    2 years ago

    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
)