Skip to main content
cancel
Showing results for 
Search instead 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

Reply
sidkasat
Microsoft Employee
Microsoft Employee

Last Date irrespective of filters

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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

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
Not applicable

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

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
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors