Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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?
Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    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

     

1 Reply

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

     

    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