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

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 Date Quarter Forecast Actual Available 1/1/2018 Q1 65 62 3 2/1/2018 Q1 67 63 4 3/1/2018 Q1 67 65 2 4/1/2018 Q2 67 67 0 5/1/2018 Q2 68 67 1 6/1/2018 Q2 68 67 1 7/1/2018 Q3 68 8/1/2018 Q3 70 9/1/2018 Q3 70 10/1/2018 Q4 72 11/1/2018 Q4 72 12/1/2018 Q4 72

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 Quarter Forecast Actual Over/Under Q1 67 65 -2 Q2 68 67 -1 Q3 70 67 -3 Q4 72 67 -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

 Q3 70 68 -2

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

 Q3 70 70 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.

• ###### 1. Re: How Do I Determine The Most Current Month's Values Inside of a Quarter

Luke,

Check out the Tableau Prep section of this post: Latest Snapshot in Tableau and Tableau Prep (Maestro) | VizPainter  Your solution will be very similar, but you will want to use Quarter as the grouping field (instead of Project in that example) and find the MAX Date.

Hopefully that gets you going.  I'd be happy to answer any specifics if you get stuck at any point.

Best Regards,

Joshua