2 Replies Latest reply on Aug 22, 2018 5:58 AM by Peter Hanges

    If current quarter then sum

    Peter Hanges

      Hi there,

       

      I have two datasets: Forecast, and Actuals.  The Forecast data shows an estimate of our bookings, while the Actuals data shows actual bookings.  I'd like to create a metric which combines these two and shows the forecast data during the current quarter and bookings data for previous quarters, so that I am presenting the most accurate data.  Each dataset has a Quarter field, which I am using to blend.  I've created the following calculated field [Forecast is pre-aggregated]:

       

      IF attr([Quarter]) >= attr(DATEPART('quarter',today())) THEN [Forecast] ELSE SUM(Actuals) END

       

      This works when I am selecting individual quarters.  Q3 shows the Forecast, Q2 shows Actuals.  My problem occurs when I select one or more quarters with the filter.  If I select Q2 and Q3 for example, I only get Actuals for Q2 and Q3 when I should be getting Actuals for Q2 and Forecast for Q3.  Any ideas what I'm doing wrong here?