Calendar Table Create Relationshiop But Doesn´t Work

[juaninavarrete]

Hi!

I have an unusual problem with my calendar Table.

I have a Fact Table called RegistroSolicitudes, in wich it has a column name "Created" (Date Time), and I have my calendar table created by:

Calendar = ADDCOLUMNS ( CALENDAR(DATE(YEAR(MIN(RegistroSolicitudes[Created])),1,1),DATE(YEAR(MAX(RegistroSolicitudes[Created])),12,31)),

"Year", FORMAT([Date], "yyyy"),

"MonthNo", MONTH([Date]),

"Month", FORMAT([Date],"MMM"),

"Quarter", FORMAT([Date],"\QQ"),

"YearMonth", FORMAT([Date],"YYYY-MM"),

"WeekdayNo", WEEKDAY([Date],2), //1-Sun..Sat, 2-Mon..Sat

"Weekday", FORMAT([Date],"ddd"),

"WeekNo", WEEKNUM([Date], 2),

"Week", "W" & WEEKNUM([Date], 2),

"IsWorkingDay",NOT WEEKDAY([Date]) IN { 6,7 } )

 

When I create the relationship between Calendar[Date] and RegistroSolicitudes[Created], the relationship is created and is active. But I cannot use it in the Report, for example in the RegistroSolicitudes table I create an Calculated Column that is:

Dia Habil =

RELATED('Calendar'[IsWorkingDay])

And none value is returnet. In the same way if try to create a Table in the report with the Date columns and other columns from the RegistroSolicitudes table, none date appears.

¿Any ideas what to do?

[amitchandak]

@juaninavarrete , Created has a timestamp in it . so create a date like this

Created Date = [Created].date

And join this with date of date table inplace of created and use.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[juaninavarrete · Accepted solution]

Thanks to @amitchandak I couldn't create that new calculated column

Instead I had the idea to create the idFecha column in the two tables as follows:

idFecha a YEAR(RegisterSolicitudes[Created])*10000+MONTH(RegisterSolicitudes[Created])*100+DAY(RegisterSolicitudes[Created])

And create the same column with the Date column in the Calendar table and relate these two columns. It's a play!

Thank you so much for your help!

[v-yingjl]

Hi @juaninavarrete ,

Glad to hear the issue is solved😀. You can accept the suitable reply as solution, that way, other community members could easily find the answer when they get same issues.

Best Regards,
Yingjie Li