Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
sidkasat
Employee
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors