1 Reply Latest reply on Oct 6, 2016 9:01 AM by Rahul Patel

    Help with Projections for Month with no Data

    Rahul Patel

      Hello All,

       

      Need your expertise on this, as have been beating around for more than a day with No results.

       

      Attached is the sample sheet to show you what I am trying to accomplish including my Projection Calculation utilizing Weekdays for the current month.

       

      Little Insight:

      C-suite wants to project current month utilizing what has already been done. Implying, for October, we are 2 Weekdays in (10/3 and 10/4). There are a total of 21 Weekdays in October. That leave me with 19 more weekdays to go (counting today).

      So, say I have 7 Quantities ordered on 10/3 and 3 quantities ordered on 10/4, I have total of 10, thus my average for the 2 weekdays is 5. I would then multiply this with the remaining weekdays in the month, so 5 * 19, which would give me 95. I would then add what has already been done in the month (10), so my total projection for the Month would be 105 (95 Projected + 10 Actual).

       

      Simply put, for every category, I have individual dashboard, with the respective projections as a stacked graph using Dual Axis. When I have a Category, for which no Quantity has been sold in the current month, I want the projection show of the Average of the Last 3 Months, however, if Data exist, then fall back on my calculation.

       

       

      The formula below works like a charm (however, feel free to make it better or identify any issues):

       

      //START CALC: Number of Weekdays remaining in the Month

      INT(ROUND(IIF( DATEADD('day', -(DAY(MAX([Order Date]))+1), MAX([Order Date]))<>DATEADD('day', -(DAY(TODAY())+1),

      TODAY())

      ,NULL

      ,(IIF(-DAY(MAX([Order Date]))+1<-DAY(TODAY())+1,

      0,

       

       

      //START CALC: WEEKDAYS LEFT IN THE MONTH

          //START: TOTAL WEEKDAYS THIS MONTH

              ATTR(DATEDIFF('week',DATETRUNC('month',DATE([Order Date])),DATEADD('month', 1, DATETRUNC('month', DATEADD('day',-1,today()))) - 1)*5

              +

              MIN(DATEPART('weekday',DATEADD('month', 1, DATETRUNC('month', DATEADD('day',-1,TODAY()))) - 1),6)

              -

              MIN(DATEPART('weekday',DATETRUNC('month',DATE([Order Date]))),6)

              )

          //END: TOTAL WEEKDAYS THIS MONTH

          -

          //START: WEEKDAYS SO FAR THIS MONTH

              ATTR(DATEDIFF('week',DATETRUNC('month',DATE ([Order Date])),DATEADD('day',-1,TODAY()))*5

              +

              MIN(DATEPART('weekday',DATEADD('day',-1,TODAY())),6)

              -

              MIN(DATEPART('weekday',DATETRUNC('month',DATE ([Order Date]))),6)

              )

          //END: WEEKDAYS SO FAR THIS MONTH

       

       

      // END CALC: WEEKDAYS LEFT IN THE MONTH

      )

      *

      SUM([Quantity])

          /

      //START CALC: Number of Weekdays done as of yesterday

          ATTR(DATEDIFF('week',DATETRUNC('month',DATE ([Order Date])),DATEADD('day',-1,TODAY()))*5

          +

          MIN(DATEPART('weekday',DATEADD('day',-1,TODAY())),6)

          -

          MIN(DATEPART('weekday',DATETRUNC('month',DATE ([Order Date]))),6))

      //END CALC: Number of Weekdays done as of yesterday

       

       

      //Adding the Actual from prior weekdays

      + SUM([Quantity])))

      ,0))

       

      The Above is shown in Sheet 1 in the attached, as it shows the projections for the current month (10/2016). I have them as pages, and if you go through, you will see under Technology, there is no Data and no Projection.

       

      To Counter the Above, I have the below, which should calculate what I need, but it does not:

       

      (IIF(ISNULL(ATTR([Order Date])),

          INT(IIF(-DAY(MAX([Order Date]))+1<-DAY(TODAY())+1, 0, WINDOW_SUM(SUM([Quantity]), LAST() - 2, LAST() )/3)),

          [Projection])

      )

       

      If you go to Sheet 2, which is how I need to present and not in the Page Format, it would not show October at all.

       

       

      Any help would be highly appreciated.