Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Date Table Data Mixup

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?

 

 

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Anonymous,

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


    Thanks,
    Lydia Zhang

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

       

      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. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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