Last Date irrespective of filters

[sidkasat]

I have a card which shows the last date in a given column. But I also have some slicers on that table and upon using those slicers, it also filters out the data and the last date changes. I want the last date to be static irrespective of the filters being used. Also the data type is Date/Time.

Tried to use Dax expression such as ALL(table) but that only worked for calculating some static numbers and not able to do MAX(ALL(Table[DateTime]) as max takes only one column. Any suggestions?

[Anonymous · Accepted solution]

[Last Static Date] :=
 CALCULATE(
    MAX( Table[Datetime] ),
    ALL( 'Table' ) -- this is how you remove all filters present on a table
)

[sidkasat]

Good idea but it didn't work. My last date in the table is yesterdays date. When I do the select of 'Latest' Table[DateTime] it gives me the correct answer. I used your solution but it's giving me 31st Dec 2019 as the last date which is not possible as the last date is yesterday's.

Any idea why this might be happening?

[Anonymous]

Mate, please send over a screenshot of your model. If you have a Date table, than it's obvious that you'll get the very last date in the table. Nothing else could be expected. This is how you pharased your problem.

If you want the last date present in your fact table, then it's a different question completely. But how can I answer it without having at least a rough understanding of your data model? How would you solve this math problem?

x + 2*<...something illegible here...> = 100

Best

Darek

[sidkasat]

Sorry I was doing MAX(Tables[DateTime].[Date])) initially as I only wanted the date. Once I removed the [Date] it gave me the correct date with the timestamp which is weird. But anyway thanks!

[Anonymous]

Yeah...

I'm going to warn you now: you are using what's called automatic date hierarchy that's created by Power BI IF YOU DON'T HAVE A PROPER DATA TABLE IN YOUR MODEL. Without a proper date table, you're not able to use the time intelligence functions like SAMEPERIODLASTYEAR, DATEADD, EOMONTH, PREVIOUSYEAR and many, many others. If you try to use them on a column that stores dates but IS NOT a proper date table, you'll be getting wrong results.

YOU'VE BEEN WARNED.

By the way, the behaviour you got with Table[Datetime].[Date] is not weird at all. This is the field of the underlying automatic date table created by Power BI and this table stores all the dates throughout all years that are present in your model. You can't see this table but you can see it in DAX Studio. However, I warn you again: DON'T USE THIS FUNCTIONALITY. Create your own proper date table. You'll then be safe.

Best

Darek