My date table is not working,

[bourne2000]

Hi

I have sales table which comes from database. It starts from 2012. My sales table as follow

I have created a date table as follow

I connected the date table and sales table through date. I am trying to create a table by dragging Month from date table and sales from sales table. However, I am getting below empty one

Can anyone advise please?

Here is the sample pbix file https://we.tl/t-rARbRR4HEO

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, seperate the date and time into 2 columns.  Then create a Calendar Table from the Date column.  Create a relationship from the Date column of the data table to the Date column of the Calendar Table.  In the Calendar table, extract Year, Month name and Month number using calculated column formulas.  Sort the Month name by the month number.  To your visual, drag Year and month name from the Calendar Table.

Hope this helps. 

[HotChilli]

You probably created the date as a copy of the Sales[Date] column and changed the datatype.  Seems sensible but doesn't work.

Try

newcol = DATE(YEAR(Sales[Date]), MONTH(Sales[Date]), DAY((Sales[Date])))

[HotChilli]

I haven't looked at the file but it will almost certainly be the dates not matching because they have times in the Fact table.  So create another column in the Fact table which is just the date (without time) and use that in the relationship.

Let me know how you get on 

[bourne2000]

@HotChilli No It's still the same