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

    Help connecting different date dimensions across data sources

    scott K

      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.

       

      Mock-Up.jpg