Forum Discussion
Helper ICalendar relationship not working
I can't figure out for the life of me why this calendar relationship isn't working.
This is my calendar code:
Calendar = ADDCOLUMNS(
CALENDAR("2019, 04, 01"," 2022, 03, 31"),
"Year", YEAR([Date]),
"Month Year Num", CONCATENATE(YEAR([Date]), FORMAT([Date],"MMM")),
"Month Num", Month([Date]),
"Month", FORMAT([Date], "MMM"),
"Quarter Num",FORMAT([Date], "Q"),
"Quarter", CONCATENATE("Q",FORMAT([Date], "Q")),
"Day", FORMAT([Date],"D"),
"Week", CONCATENATE("Week ",FORMAT([Date], "WW"))
)And I'm trying to connect date to my created date column in another table.
Both have the exact same data format and they genuinely do match up, so I have no idea why the relationship isn't working!
I've used the exact same calendar table in other reports and it works fine so can anyone help figure out why this one doesn't?
Any help would be great thanks š
3 Replies
- mahoneypat
Microsoft Employee
Both columns are datetime format, and the time component does not match, which is why it isn't working. If possible, you should convert both the type Date.
Pat
I have the exact same issue. I did convert both relationship columns (in this case Date & created) into Date Type format yet it does not work.
Am I missing something? Is it necessary to have every single date from Calender table to have a record in Ticket table? I have wider date range in Calender table (Jan 2019 - Dec 2025) however data in Ticket table is only from Jun 2019 - Dec 2021. Does this impact the relationship in anyway?
