Extract Quarter from Period Budget Table

[pete234]

I just importe a new version of quotas to link to my powerpivot sales data.
I want to get actual to budget in my weekly reports to show quota goal to quarter.
But I only have the quota by period and many are uneven distributions.

I have my budget table imported, it has Period #, Budget, Plan ID, Period Begin Date, Period End Date.
I have a table of unique plan ids as well, that joins to sales rep table.
I assumed I would use fiscal calendar to connect the budget but I need the main report by week.
Before this change I had the quarter fixed in the data and then I could just divide by the sales totals, then having a running total
of % to quarter. It was working nicely.

So in a nutshell, I report by week but need to show actual to Qtr budget, not week.
What type of calculation could achieve this?

 

[pete234]

I tried pivot on budget using rep name and I get "needs relationship."
I have the planid linked to unique plan table, this is a foreign key in the db it is from.
Then this connects to the budget table. If I just pivot unique plan table to budget it works but that's really no the point,
I need the plan tied to sales person.

 

[Jacob Barnett]

Pete, its slightly difficult to help based on the information provided but to me it looks like you have a basic problem with the granularity of your budget - if your numbers are quarterly there is no real way to report weekly on QTD progress without agreeing some kind of phasing. After that a separate date table that contains your fiscal calendar including a column for QTD should make the comparison straightforward.

In terms of the relationship issue it basically sounds like you have no link between the person and the budget - does each PlanID have a unique sales person?

If you were able to upload even a small amount of 'fake' sample data to SkyDrive or similar I'd be very happy to take a look.

Jacob