3 Replies Latest reply on Jul 19, 2016 5:38 PM by John Hopkins

    Monthly budget, daily actuals, and YTD comparisons: level-based measures?

    John Hopkins

      I've got an issue that's in my OBIEE blind spot. Really appreciate the input.

       

      We have a "Appointment Measures" fact, with a LTS at the "Day" time grain. There are several other dimensions that make up the granularity, but the most relevant one is "Scheduling Department". 

       

      We also have a budget by month and Scheduling Department. I've included this table as a separate logical table source in the "Appointment Measures" fact. I also have this table tied to the "Appointment Date" dimension by the first date of the month.

       

      So,

       

      Appointment Measures

      • Appointments Daily Fact table
      • Sched Dept Monthly Budget table

       

      I want a single query that returns:

       

      Actual Appointments Month-to-Date

      Budgeted Appointments for the Month

      Fiscal YTD Actual Appointments (for example, through 7/17/2016) - defined in metadata using ToDate function

      Prior Fiscal YTD Actual Appoints (in this example, through 7/17/2015) - defined in metadata using AGO and referencing Fiscal YTD function

      Fiscal YTD Budgeted Appointments (would include the July 2016 amount)

       

      Is there a way to model this (with, I assume, level-based measures) such that time series functions work as expected?

       

      Thanks,

      John