I built an SSAS Tabular model cube and created a date table using calendarauto(). I have marked it as a date table and created a relationship with the fact table using the date column, and both columns have a Datetime data type. When I analyze the data using excel, the date filter is not working correctly. For example, when I filter on the year 2021, it gives me row values for the year 2019 also, but if I use the date column from the fact table, I get the correct results. When I analyze the Tabular cube using Power BI, it works right. Could you please suggest what exactly is going wrong with our date table?
When I put the Fact table date column and date table date column on excel it looks like the screenshot below.
First, I think that is a bad IDEA to use CALENDARAUTO because it searching for all date columns from your model (and if you have a Customer with a born date eg. 1912-02-02, then you create a big table from that date).
A better idea is to use CALENDAR where you have more control
To the topic. Are you sure that you make a relationship on THIS column (in your fact table)? Check also Cross Filtering Behavior -> OnDirection/BothDirection; Show your relationship detail.