Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Date table returns blank months

Hello!

 

I have a dataset with sales data in one table and two dates table, calculated with dax:

Table name = 

ADDCOLUMNS (
CALENDAR (DATE(2018;01;01); DATE(2025;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; YEAR([Date])&"/"&MONTH([Date]))

 

When I insert yearmonthnumber and sales amount to a graph, I get the following:

 

 

When I press on the blank graph, it filters all the values and those lines have a date in the corresponding sales table. Like this:

 

Sales Table dateCalendar date 
1/1/2018blank
1/1/20181/1/2018
30/1/201830/1/2018
1/2/2018blank

 

In some way, for some rows the relationship seems to be lost. And it is not specific for any same dates, like the 1/1/2018 could be blank for some and not blank for some other. To further add to this, when in the data tab, I can't find any value in either columns that contains blanks.

 

I have no idea why this happens. All the date fields are the same formatting and relationships are created with many-to-one and marked as date table. 

 

Thanks for any help.

 
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    What seemed to do the trick was to in the query editor, go to the transform and extract the datetime value to just date.

     

    Thanks for the help.

7 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    6 years ago

    Anonymous ,

     

    Take table and try to debug your data by dragging fields like YearMonthnumber, DateCOlumn, and sales amount. And take a closer look on Blank value of YearMonthnumber and its corresponding Date Column value.

     

    Don't forget to give thumbs up 👍 and accept this as a solution if it helped you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I have, and it seems random. Can't find anything weird.

      For me it seems to be some problem with the relationship.

      • Tahreem24's avatar
        Tahreem24
        Super User
        6 years ago

        Anonymous  If possible so, can you please upload your PBIX file with Date Column and new Columns which you have created for Calendar only and mask your remaining data. SO, that I can check at my end and try to find the root cause of that issue.

         

        Don't forget to give thumbs up 👍and accept this as a solution if it helped you.