Date Relationship Issue (Missing Data)

[Ryaneb85]

Hey all,

Struggling with an issue I can't resolve.

I'm trying to include a very simple column chart plotting Date by Invoice Total. The problem is that some data is being missed/ignored by this and the Invoice Total is incorrect as a result

I have an 'Invoiced' table and the InvoiceDate column holds the date the item was invoiced. If I put this field into a table visual it lists all the invoice dates (filtered by July) and the data is correct:

I have a date/calendar table that is working fine with every other relationship, but if I drag the 'Date' column from this, you can see that some of the invoice dates drop out. I assume because there is some issue with the relationship:

here is my relationship:

 

I've ensured the Data Types for both fields are set to Date and the Format is set to Short Date. I've also formatted the columns in Query Editor to ensure these are set to Date too as a last ditch effort, but the result is the same.

There seems to be some kind of date incompatibility but I cannot see why.

Any thoughts?

Thanks,

Ryan

[calerof]

Hi @Ryaneb85,

The cardinality for the relationship is the other way around. Calendar table (1) to Fact table (*).
Make sure your calendar table includes all the dates from the specified period.

Cheers,

Fernando

[Ryaneb85]

Thanks for the reply @calerof 

Just to clarify, are you saying the Calendar Table should be 1 and the Fact Table should be *? If so, I already have it set up this way. Dates is my Calendar table and Invoiced is my Fact table.

I've tried swapping the cardinality around just to be sure so that Calendar Table is * and Fact Table is 1, but it won't let me make this change.

I have the necessary dates included in the Calendar Table too and double checked again to make sure.

[calerof]

Can you show me your Calendar table?