Whether you’re joining, blending or relating data, having different dates in both tables may give you some trouble. For example, if you have different tables for sales and sales targets over time, the target table may have some later dates to display future targets, which won’t appear in the sales table. On the other hand, the sales table might have old sales, for which no future targets were set.
When you put the two tables together, there will be rows where one table has values corresponding to a date, but the other table has null values…
Visualising targets as gantt bars above sales bars would result in the old sales getting bunched together in one bar for null values, with future targets not appearing at all once you filter for company.
To correct this, we create a calculated field:
Essentially, the field will display the date field from the targets table, and the date from the sales table if the one in the targets table is empty.
You have to do this for the company field as well.
If we use the corrected date field instead of the date from the sales or target tables, we now have both old sales displayed per month, together with future targets on the same chart.