Can't get date relationship/changing column type to date only to work

[Cooper01]

Hi all, brand new to Power BI. I am trying to create a date table and then a relationship between that and one of the tables (Sales) from my data source. Initially I created the table (calling Calendar) using the CalendarAuto() function and then added some other columns like year, month, etc. I then went to create the relationship between my date columns in Calendar and Sales. 

I created a table visual where the values were Total (a column in Sales) and Year (from Calendar). I changed Year in the Values section to Don't Summarize. It just shows a blank year and all sales, leading me to believe there is an issue with the relationship.

Based on some initial googling I think my issue is that the date field I am using from Sales is actually a date time, so I don't think it can find a match from Calendar dates. Therefore I added a new column to my Sales table and set it to the original date time field, and then changed the Data type of my new field to Date.

I then removed my relationship and created it between the date field in Calendar and my new custom column in Sales, however all years are still blank.

Am I going about changing the column type incorrectly? Is there anything else I might be missing here? Thanks in advance.

[Cooper01 · Accepted solution]

@Ashish_Mathur @v-stephen-msft thank you both for the replies and sorry for the slow response. The issue was that the date time column I was using as my date column reference was not truly a date time but text. Didn't notice because it looked like a date time and there was no issues creating a date column from that. Once I changed the reference field to a true date/time, it worked.

[v-stephen-msft]

Hi @Cooper01 ,

It may be caused by the wrong filter direction of the relationship.

You can refer to this official document.

It is recommended to change the Cross filter direction to Both to try.

Best Regards,

Stephen Tao

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

[Ashish_Mathur]

Hi,

It looks like you have performed the correct steps.  Share the link from where i can download your PBI file.  If you imported data into the PowerBI Desktop file from an MS Excel file, then i will need that MS Excel file as well.