Connecting the Dates to the Calendar Table not working (date slicer)

[Anonymous]

I created a Calendar table that lists all dates from year 2000 till year 2030. I have multiple tables that each have a CreatedDate column. I connected each table to the Calendar table via relations. Then, I created a slicer and used the Calendar Table row as the field. It's supposed to filter all the visuals by selected slicer date. However, it is not working. Is there something I did wrong?

[dwlashua]

I had the same issue. The problem is that changing the data type to date/datetime in the Report View does not actually change the data type. It basiclaly just changes the formatting of the date.

Instead, you need to change the data type in the Power Query editor (right click on the query and click Edit Query), then select the data column that needs to be changed and right-click to open the menu and got to "Change Type".

Alternatively, while in Report View you can right-click the query to create a new column and use DAX to create limit the data type to date only:

NewDateColumn = 'QueryName'[OldDateColumnName].[Date]

Check out this video for additional explanation: https://youtu.be/pux4Tsyv8TQ

[Bogdan2021]

Hi,

Same problem here 🙂 , i have 1 calendar table and 2 other tables. In one table the filter works in the other table doesn't. I have checked all from the above and it doesn't work. Any ideas?

[jrsommer]

Hello, 

First I know I'm bring this back from the dead... but I recently had this issue and came across this thread. Then I found my solution so I just wanted to post it here, for the next 'me' that comes along.

When I open PowerQuery/Transform Data to view the tables one of my fields was a date/time format, like mentioned above. The other field I was trying to relate it to was Date. So all I had to do was add a step to convert it from Date/Time to Date. Once I applied the new step, it worked!!

[pmika]

Thank you, this worked for me! 

[Anonymous]

I had this problem today after I changed the data source of a few of my tables in a report. The slicer that previously worked on the calander table stopped working to give blanks for the date.

I converted the date value on the table from datetime to date to match the calander table : did not work

I converted the date value on the table from datetime to date in Transform Data at the time of import : this worked.

So the data type needs to match during the import so there should be a step something like :

#"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}})

which in my case changes datetime to date.

[Anonymous]

In DAX, date can be stored in two data types. 

1. Date/Time
2. Date

Based on what you have mentioned, I can think about only one possibility for the error. If the date column's data type in one table is "Date" and the data type of date field in the other table is "Date/Time", then there is a chance of error as mentioned in your post. 

As a first step, please confirm that the data type of the date column is the same in all your tables.

[skarim]

Hi

I am new to power Bi . I am trying to get data from Multiple Sheet from Excel . The are all connected to cost codes.

Sheet No 1 is Invoice Cost

Sheet No 2 is Labor cost

If I click on lets say April 2019 then it will only show me the labor cost for the cost codes which are present in Sheet 1 although I know there are more cost codes for Labor in April 2019.

It should display all the cost for Labor even if its present in Material or not.

looking forward for your feed back

Cheers.

[Anonymous]

I checked every table. All of the columns are Date/Time.

[Anonymous]

Check the filtering direction also.

[Anonymous]

The filtering direction is all Single.

[Anonymous]

Is it possible to share the pbix file?

[Anonymous]

Unfortunately no, since it's confidential company data. I am willing to give more details. Just let me know what you need to know about the file.

[v-piga-msft]

Hi @Anonymous,

Could you share the screenshot about the relationship of the tables?

Best Regards,

Cherry

[Anonymous]

Hello @v-piga-msft , here is a screenshot. I had to omit the table names and other columns because of confidentiality.

The circled columns are the ones connected to the Date table in the middle. The Date table holds all dates from year 2000 till 2030.

If you need more info let me know.

[TomMartens]

Hey,

I'm wondering if the datetime values match. Creating a calendar table using the CALENDAR function creates datetime values where the timepart is 12:00AM (midnight). Maybe the timepart of the other datetime columns prevents a proper match. If this might be the case. I would recommend that you change the datatype of the date column in your calendar table to date and that you add a new column to the other tables extracting just the date part from the CREATED.... columns.

A last thing you have to check: what datetime column are you using on the visuals, make sure that you are using the column from the CALENDAR table.

Regards,

Tom 

[marjanR]

I have the same problem. Every thing is correct. related columns are date. But filter doesn't work. It shows as there isn't any data on that date I fitered.

[nataliafm31]

Same here. Did you find any solution, marjanR? 

[Anonymous]

I'm right here with you, Natalia... All my columns are formatted as "Date" but the relationship isn't working.