Centralized Calendar

[alycianw]

I am trying to connect a single table with a working centralized calendar table. I have created the basic calendar table, marked it as a Date Table, Created relationship between Calendar Table on the Date column and the Change Request table on the sys_created_on column. I still cannot use the calendar table as either a slicer or in a visual to display data by year.
I am just trying to get this very simple connection and report to work so I can create a centralized calendar for another report I have with 3 tables that each have 2 dates in them that need to be tracked. Having the same issues on that report so wanted to start by getting something simpler to work as intended.

Relationship Setup:

Relationship Setup

Expected results when using calendar slicer

Expected results when using calendar slicer

What it shows by default, the top graph uses the date/month from another date filed in the original table, second uses calendar year/month and does not work

What it shows by default, the top graph uses the date/month from another date filed in the original table, second uses calendar year/month and does not work

Calendar Fileter showing all blanks

Calendar Fileter showing all blanks

[Kaviraj11 · Accepted solution]

What is the data type on system_created_dt in Change Request table?

If it's a datetime, make sure to change it to date data type in the source or pwoer query.

[Idrissshatila · Accepted solution]

Hello @alycianw ,

it's as @Kaviraj11  mentioned, this behaviour occurs when the date type column is linked to the datetime column.

and it's in your fact table and would only work when changed in Power Query.

[Kaviraj11]

Are you using the month year column from Change Request table? If yes, make sure to use it from Calendar table.

[alycianw]

the bottom graph is using the year/month name from the calendar table, but it is now showing in chronological order,

[Idrissshatila · Accepted solution]

@alycianw , you should sort the month column by the month number column.

check to see how https://youtu.be/oNXZbi8BGMs?si=3PCs83HgbtCG2w4u

[Idrissshatila · Accepted solution]

Hello @alycianw ,

it's as @Kaviraj11  mentioned, this behaviour occurs when the date type column is linked to the datetime column.

and it's in your fact table and would only work when changed in Power Query.

[Kaviraj11 · Accepted solution]

What is the data type on system_created_dt in Change Request table?

If it's a datetime, make sure to change it to date data type in the source or pwoer query.

[alycianw]

That worked, thank you so much!

But I have one more question. Do you know why now my year / month name are not in the correct order?