Date Table Data not populating in reports

[AmberJane]

Hello, I am recently back from Maternity leave and running into an issue I figured out before but can't recall how. 

I have a date table and an alert ticket table. I created the relationship between the two table. I am trying to pull the Week & Year from my Date Table against the Created Date on my alert table but it populates blank. 

[AmberJane]

I changed the filter to just show the last two weeks and now all the fields are populating... What is this life!!! 

[AmberJane]

I edited the relationship to the new calculated column and it seems to be working.

I take it back its still not working lol. If I use the Date and Week & Year from the Date table compared to the count of ticket IDs from the alert table it works but as soon as I add the Dates from the alerts table it breaks 😞 

[AmberJane]

Changed the column type to Date from Date/Time and still not populating. 

Created a calculated column and also did not resolve the issue 😞 

[marcelsmaglhaes · Accepted solution]

Hey @AmberJane !

The issue you're encountering with the two tables related by a Date column stems from a data type mismatch. One table contains the Date/Time data type, which includes both date and time information, while the other table has only the Date data type, which stores dates without the associated time component. To resolve this issue, you have a few options:

1. Modify the Data Type:

You can modify the data type in one of the tables to match the other. If you choose to retain the Date/Time data type, you can format it to display only the date portion. Conversely, if you convert it to the Date data type, the time information will be truncated.

2. Create a Calculated Column:

In the table with the Date/Time data type, you can create a new calculated column that extracts only the date part of the Date/Time column. For example, in Power BI or Excel, you can use a formula like DATE(YEAR([DateTimeColumn]), MONTH([DateTimeColumn]), DAY([DateTimeColumn])) to create a new Date column with just the date.

**If this post helps, please mark as solved. 

Regards,
Marcel