MAX date within a column of dates (in a FACT table) that is less than or equal than today

[CLEARIFY]

I am looking for a simple calculation that finds the maximum date in a FACT table date field, that is less than or equal to todays date, and would say something like this:

CALCULATE(MAX(Table.Date where Table.Date is less than today

I have attempted many options including filter, etc. but not getting the correct value.

So for instance if I had a list of dates:

1/1/2020

6/3/2020

10/13/2020

12/15/2020

and today is 10/14/2020  my return would be 10/13/2020

I am using this to load a single variable that must contain the actual date (e.g. 10/13/2020.

[v-kelly-msft]

Hi  @CLEARIFY ,

Is your issue solved?

I think what suggested by @amitchandak  may solve your issue, I made a pbix file for details if you needed,see attached.

Measure is as below:

Measure = CALCULATE(MAX('Table'[Dates]),FILTER(ALL('Table'),'Table'[Dates]<TODAY()))

And you will see:

If your issue still isnt solved,pls let me know.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@CLEARIFY ,

Edited - Just saw already answered. It need < in this case.

Try like

calculate(max(Table[Date]), filter(Table, Table[Date] <Today()))

[MattAllington · Accepted solution]

Try this
CALCULATE(MAX(table[date]),filter(all(table[date]),table[date]<=today()))

It does depend if you are using a caledndar table, and how you will display the result, but i hope it helps.