cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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.

1 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.

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
3 REPLIES 3
Community Support

Hi  @CLEARIFY ,

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

Super User

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

Try like

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

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.

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.