Relationship between dates

[leandroparischi]

Hi, some time ago I made a CALENDARAUTO() table for date reference. I use in every project i work (dCalendario)

But when I need to create a relationship between dCalendario.Date and table1.DueDate my DueDate field loses his Date type and show as Text type.

I cant use the fields Year or Month from dCalendario because their dont reference my DueDate.

Any idea to solve this? Thanks and sorry for my bad english

[v-lionel-msft · Accepted solution]

Hi @leandroparischi ,

Like this?

After creating relationship

This is normal. After the relationship is created using the date column, only the date column of one of the tables can use the hierarchy. You can do more tests yourself.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

@leandroparischi 

maybe it would be better to create some surrogate date key in both of tables with the same format with Power Query?

like "YYYYMMDD" formatted as text 

Text.Combine({Text.From(Date.Year([Date])), Text.From(Date.Month([Date])), Text.From(Date.Day([Date]))})

[amitchandak]

@leandroparischi , Change the data type in edit query mode . If not under column property

[Greg_Deckler]

@leandroparischi - That's really odd. Have you tried setting your columns in both tables back to Date or Date/Time. 

[leandroparischi]

@Greg_Deckler I tried do this in my DueDate field.

In Power Query, the Due Date field is categorized as a Date type. Shows the date icon and etc.

In the Column Tools tab, it shows the type as Date as well.

But it is not working as a date field and I believe that is the reason for my dCalendario table can't use DueDate field as reference.

[Greg_Deckler]

@leandroparischi - I was thinking to try it in the "Column Tools" section of the ribbon when in Report view.

[amitchandak]

@leandroparischi , can share some sample screenshot

[leandroparischi]

Sure.