what's the difference between 'table'[date] and 'table'[date].[Date]?

[hongjyan]

Hi, guys

I created a table via CalenderDate = CALENDAR(DATE(2019, 01, 01), DATE(2020, 12, 31))

then, create a column via Month = FORMAT(CalenderDate[CalenderDate], "yyyy-mm")

then create a measure named max1 via max1 = MAX(CalenderDate[CalenderDate])

then create a measure named max2 via max2 = MAX(CalenderDate[CalenderDate].[Date])

then use column:month as filter to get corresponding max1/max2.

Result is as following:

why? why filter doesn't apply on max2?

[Mariusz · Accepted solution]

Hi @hongjyan 

Hopefully, this article will help you to understand what [Date].[Date] is 
https://www.sqlbi.com/articles/automatic-time-intelligence-in-power-bi/

In your example, max2 is returning a max of all dates in automatic date/time table as Month column is in another table that is not propagating the filter down to auto date table.

If you have a date dimension in your model best practice is to switch off automatic date/time and not use it.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn