Missing dates - Estimated vs Real Line Chart - Can't find a solution

[tgom94]

Hi, there! 

So I'm new to Power BI and there's this problem I can't seem to find a solution.

I have table 1 and table 2.

Table 1 contains date and real sales value.
Table 2 contains date and estimated sales value.

I'm trying to plot a chart to compare real vs estimated sales value at Y axis and date at X axis - pretty simple.

To do this, I've linked the Date columns in both tables at the Relationship view - 1 to N, since I can have multiple sales in one day, but only one total estimated value per day.

The problem is that Table 2 has not estimated values to all the dates that Table 1 contains.

This means that Table 1 has dates like (03/07; 04/07; 05/07; 06/07 - and so on), where Table 2 has only dates (03/07; 06/07) - because the estimated value wasn't estimated every day, but just in a couple of days.

The result is a line chart with a lot of values in an "{empty} date value"

How can I fix that?

Thank you so much!

[Anonymous · Accepted solution]

Hi @tgom94 ,

You can create a calendar table with whole date range, then use this as bridge to link two tables. 

When you use calendar date field as category fields, it will mapping and summary all records based on relationship.
Regards,

Xiaoxin Sheng