mark / tag latest date (would filter be necessary in calculate ?)

[JK-1]

Have had some helpful RANKX advice in the past and researched some MAX , LASTDATE situations but still not sure the best way to attempt / resolve something new for the below.  Looking for the return to give a final unique entry marker on the relevant row which is going to be based on the latest date of said Store Number up to a current date [today, or in the example given 31/05/2025] to deal with how future dates shouldn't be selected as latest/ LAST available row in the data.

Grateful for pointers, many thanks

			return marker
Store	Target	Target Date	Latest - && DATE<=(2025,5,31)
15	21	12/01/2025
18	68	01/01/2025	"Last" / 1 / Etc.
23	17	01/02/2023
23	28	02/07/2024	"Last" / 1 / Etc.
7	18	02/02/2024	"Last" / 1 / Etc.
4	81	06/06/2025	[               ]  single entry, but nothing as future date
7	33	11/10/2025
15	44	19/02/2025
15	9	03/03/2025	"Last" / 1 / Etc.
15	14	05/08/2025

[Ashish_Excel · Accepted solution]

Hi,

This calculated column formula works

=if(CALCULATE(MAX(Data[Target Date]),FILTER(Data,Data[Store]=EARLIER(Data[Store])&&Data[Target Date]<today()))=Data[Target Date],"Last",BLANK())

Hope this helps.

[JK-1]

Thanks again for this @Ashish_Excel  

A further column has been introduced [Completed Date].  How could I adapt the condition to firstly look that Completion Date isn't blank, before performing the remainder? Am slightly stumped there

And a separate follow-on if I wanted to uncap the TODAY Target date including only less than, what would be the best way to drop that out of the condition? I've tried a few things but haven't cracked that either yet.

=if(CALCULATE(MAX(Data[Target Date]),FILTER(Data,Data[Store]=EARLIER(Data[Store])&&Data[Target Date]<today()))=Data[Target Date],"Last",BLANK())

Thanks

[JK-1]

Thanks - amazing!

[Ashish_Excel]

You are welcome.

[Arash_Bhz]

I am not sure what the level of granularity is, assuming store number and your datatable is called 'stores', create a calculated column to mark each row so you can filter the Future one out by slicer to exclude future dates, Here is the formula for the column:
Marker = 
VAR CurrentDate = 'StoreTargets'[Target Date]
VAR StoreID = 'StoreTargets'[Store]
VAR LatestValidDate =
CALCULATE (
MAX('StoreTargets'[Target Date]),
FILTER (
'StoreTargets',
'StoreTargets'[Store] = EARLIER('StoreTargets'[Store]) &&
'StoreTargets'[Target Date] <= TODAY()
)
)
RETURN
IF (
CurrentDate <= TODAY(),
IF (CurrentDate = LatestValidDate, "Last Valid Date", "Valid"),
"Future, Non Valid "
)