Date table returns blank months

[Anonymous]

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 date	Calendar date
1/1/2018	blank
1/1/2018	1/1/2018
30/1/2018	30/1/2018
1/2/2018	blank

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.

[Tahreem24]

@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]

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

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

[Tahreem24]

@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.

[Anonymous]

I will do that.

Just before, is it possible that even though the date is changed to the format "Date", it is still stored as "Date time"?

[Tahreem24]

@Anonymous , Yeah. Format type of Date is DateTime as per formatting option under Modelling Tab.

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

[Anonymous]

I changed the query under the transform tab for the sales table date from date/time to date.

Still, if I format under the modeling tab to date/time I get 2 different times 1/1/2018 00:00:00 and 1/1/2018 12:00:00.

When I filter on the blank yearmonthnumber --> all the fields are 1/1/2018 12:00:00.

This suggest there's something with the formatting. Any way to fix it? I don't want to have the clock in the dates table, but rather remove the time in the query. But that seemed to not be possible?

[Anonymous · Accepted solution]

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.