Date Table Data Mixup

[Anonymous]

Hi, 

I have created a date table within my report that pulls out the month and year from the given date. 

However, when using filters, the data is not being allocated to the specific year:

The relationship is O-M, and it cross filters both ways. Where am I making the mistake?

[v-yuezhe-msft]

Hi @Anonymous,

Could you please share sample data of your table and post expected result here?

Thanks,
Lydia Zhang

[Anonymous]

The 1st is example data. The 2nd is what the table looks like without a dateTable being created. 

I want to create a date table, and still achieve these desired results. 

[v-yuezhe-msft]

Hi @Anonymous,

The above issue is caused by that you have different DateTime values in the original table, but in your dateTable, I guess that you only have date values or datetime values that only contain 12:00:00. In this scenario, not all the datetime values in your original table match the date values in your dateTable, and you will not get expected result when you drag Year from dateTable.

To solve this issue, create a new column in your original table using the DAX below, make sure that you change date type of the new column in your original table and Date column of dateTable to Date type. Then remove the existing relation between two tables, create a new relationship using this new column and date column in dateTable.

Datecol = DATE(YEAR(Table1[Date]),MONTH(Table1[Date]),DAY(Table1[Date]))

Thanks,
Lydia Zhang

[Anonymous]

Hi @v-yuezhe-msft 

So do i need to extract the date for each datetime field in my tables?

What about the possibility to remove the seconds from the datetime table and create a date table with a record for each minute?

Too much data? 

Cheers

[Anonymous]

@v-yuezhe-msft