I'm having trouble making a pivot table that will show me the running sum of our sales broken out over order days prior to opening, next to the season goal amount, and then track the running sales to goal. Here's what I'm trying to see:
Order days prior to Open Total Paid/Reserved Value Goal % to Goal
-4 250,000 1,000,000 25%
-3 500,000 1,000,000 50%
-2 750,000 1,000,000 75%
-1 1,000,000 1,000,000 100%
Has anyone build anything like this? What is the trick to getting the goal to stay static (based on budget amounts for all the performances in one season)?
I've tried suggestions I've found in other posts (like this one), but the goal amounts shift over each day prior to opening.
Any help is always greatly appreciated.
Thanks,
Michael
If you're not hardcoding your goal and it's instead coming from the Performance Budget Amount, this post talks about that a bit:
(+) Rolling Sales as they approach budget goal - Reporting & Analytics - Forums - Tessitura Network
And a formula like this for the Goal:
MAX ( [Order Weeks Prior to Open] , SUM ( [Performance ID] , [Max Budget Amount] ) )