Excel Power Pivot
Lookup Table = Calendar;
Data Table = Sales
Pivot table Rows: Calendar[Year]
Pivot table Values: [Measure] = CALCULATE(SUM(Sales[Amount]); Calendar[Date] < DATE(2003;01;01))
Measure does not respect the filter context Year and returns:
Year 2002 -> sum of sales from the earliest date in Calendar to 01/01/2003;
Year 2003 -> the same
As Date column is not in the filter context - Year , I would expect the Date filtration added to the Filter context not override it. The Measure should return then:
Year 2002 -> sum of 2002 sales;
Year 2003 -> 0
Why does the actual result differ from the expected?
I have spent half a day trying to figure this out and could pinpoint the source of the issue to the format of the Calendar[Date] column. As soon as you switch its format away from a date format to a number format, the filter context on the Calendar[Year] column is respected.
After further research, I have found the following passage inside this article https://www.sqlbi.com/articles/mark-as-date-table/:
"In order to simplify the usage of time intelligence functions, the DAX engine makes an assumption when two tables are related through a column of Date data type: When a filter is applied on the key of the relationship – Date[Date] in this example – the new filter overrides any other filter on the Date table. It basically applies a REMOVEFILTERS ( Date ) to the filter context every time you apply a filter on the Date[Date] column. This behavior occurs automatically only when the relationship is based on a column of Date data type."
It seems insane to me that such a detail is nowhere mentioned in the official documentation (or maybe I just haven't found it yet).