How to link 2 facts with dates (forecast & as-is) correctly?

[andreas_koehler]

Hi all,

I have 2 sets of data in my example. One with sold stock of certain volume for a certain price (for 2016) and one with the annual forecast of price and volume for 2016,2017,2018.

Both sets have share the fields 'company' and 'product' of which I created a complex key.

The order tables has dates called 'PostingDate' and the forecast table has dates called 'ForecastDate'.

What I want to achieve is to show forecast data and as-is data in one graph with shared time dimension.  This should be a very common application.

After sifting through the documents here in the community I chose Rob's great

Linking to two or more dates

but it does not work out:

The  'Year'-field of the calendar does not act as a filter for the data in the ForecastTable.

Why?

Any help would be very much appreciated.

Andreas

Discl.: The attached examples use Rob's Calendar sub.

[r3iuk]

Hi Andreas,

Attached is a solution.

Cheers,

Mike

[andreas_koehler]

Hi Mike,

great to hear from you and hope you are well.

Thanks for your solution.

Originally I wanted to avoid a solution of concatenating both files because I wanted to keep the main fact table (actual) as near to the source data as possible and be able to change the forecast data in a reload. The 'actual data' is in real life huge and the forecasts much smaller, so I wanted to keep them separate.

But your solution to concatenate both with a marker field [Data Type] does the job in a nice straight forward way.

Could you also figure out why the code in the example did not work? Another reason for this attempt was to understand how to deal with multiple facts tables with dates.

Thanks and take care,

Andreas

[r3iuk · Accepted solution]

A couple of problems:

1. The key you created to link the actual and forecast data only contains the customer and product references.  You also need to include the Year into the key.

2. The Company and Product names are in the Posting table so are not available for display for the 2017 & 2018 forecasts which have no posting records.  You need to move them into a separate table so that they are available to both fact tables.

See attached QVW