1 Reply Latest reply on Aug 28, 2018 11:30 AM by Joshua Milligan

    How Do I Determine The Most Current Month's Values Inside of a Quarter

    Luke Hinson

      Within the quarter, I want to use the most current month's values  to report on Forecast, Actuals and calculate Remaining Available. 

      (Tableau Prep 2018.1.1)

       

      For example, a simple headcount forecast by month would look like this:

        

      Headcount Data with Monthly Forecast and Actuals
      DateQuarterForecastActualAvailable
      1/1/2018Q165623
      2/1/2018Q167634
      3/1/2018Q167652
      4/1/2018Q267670
      5/1/2018Q268671
      6/1/2018Q268671
      7/1/2018Q368
      8/1/2018Q370
      9/1/2018Q370
      10/1/2018Q472
      11/1/2018Q472
      12/1/2018Q472

       

      For reporting, I want to show this by Quarter.

      I want to use the last month of the quarter to establish Forecast (done:  thank you Michael Someck https://community.tableau.com/thread/243205

      I want to use the most recent month with Actuals in the quarter for Actuals.  THIS IS MY CHALLENGE. 

       

      Quarterly Reporting
      QuarterForecastActualOver/Under
      Q16765-2
      Q26867-1
      Q37067 -3
      Q47267-5

       

      So the 65 actual headcount in March was used for Q1, the 67 actuals reported in Jun was used for Q2, and until another actual value is reported, the 67 is carried forward and used to determine the available/over/under values.

       

      In July, if actuals are reported as 68, then Q3 would change to

      Q37068 -2

       

      and if August actuals were reported as 70, then Q3 would change to

      Q37070 0

       

      I know the answer but have been unable to get PREP to identify and change the actuals w/in the quarter.

      Thanks for any help/suggestions.