3 Replies Latest reply on Oct 20, 2016 1:07 AM by Łukasz Majewski

# Help connecting different date dimensions across data sources

In one data source i have revenue data by Period.  (We have 13 periods per year, 4 weeks each)  In the other data source i have a list of future projects by Start Date, End Date, and Total Expected Revenue.   My question is how can i combine these data sources so that i can show actual revenue for periods YTD and forecast for future periods - and a combination of both in the current period?

Challenges:

1.  The start and end dates from the future projects must be converted into Periods.  This can be done in a simple calculated field.

2.  Then to calculate forecast, i can simply divide the total expected project value by the number of periods since i know start and end dates (not scientific but a good start at least)

3.  But I assume that i need to perform a fixed or include Level Of Detail calculation in order to aggregate the future projects by period or by account.  I have not been successful in this last step

I am hoping to build something similar to the mock up below that shows Actual revenue for Periods 1-4 (in the lightest blue) and forecasts for future periods (shown in other shades of blue).  There will be times when we are in the middle of a time period and would need to show both Revenue and Forecast in a single period so having two separate charts side by side is not a solution.

Your input would be greatly appreciated.

• ###### 1. Re: Help connecting different date dimensions across data sources

Can you define date range for period 1?

• ###### 2. Re: Help connecting different date dimensions across data sources

Sure - period 1 would be 6/5/2016 - 7/2/2016.  Thanks!

-Scott

• ###### 3. Re: Help connecting different date dimensions across data sources

Hi,

1. I think your sample is missing keys and you can only join it now on dates/periods which makes my effort somewhat pointless
2. Since forecast table periods are longer than 1 period you must use dates range when joining (>= and < or 'between' operators) which cannot be done with blending or standard excel join interface
3. I have attached a wb with a legacy excel connection with custom join and your excel file with another table in it with the same join (I named your table 'revenue' and 'forecast')