Some dates not recognised as dates & creating 'blank' column in bar chart

[Jsummers]

I have imported 2 tables from SQL and created a calendar table.  In the 2 imported tables, there are 2 columns with dates in them, some of the dates might be the same, some are different.  I have created the relationship from the 2 imported tables to the calendar using the date columns.  I have then created a bar chart for one table of completed audits dates, this produces a bar chart counting the dates per month and displaying correctly.  When I repeat this for the second table (scheduled audits), some of the dates are not recognised and display in a column entitled 'blank'....but they are not blank.  On the scheduled audits table, there are 99 rows and all have a scheudled date in them.  The total on the bar chart (blue) comes to 99 also.

This is what the date column looks like for the scheduled audits, it is date in the data type and formatted the same as the completed audits date column.  

Table relationships

This is the same settings for both relationships:

Please can someone advise how I get these dates to be recognised as dates so the bar chart shows correctly?

Many thanks

Julia 

[Jsummers · Accepted solution]

I have found the solution, even though in data view the date column was set as date, when I went into transform data, the date column was not set as date.  I have now set that as date and my chart is looking correct!

[Mahesh0016]

can you share demo_data file for more understanding.

[Jsummers]

After some more playing around, I have found that it is something to do with the calendar and the relationship between the 2 tables.  If I load the dates in and create a bar chart without linking to the calendar table, the bar chart is correct.  If I create a relationship between the scheduled dates & the calendar, then some appear as 'blank'?