Actual vs Budget: multiple measurements, periods

pete234

Active Member
Joined
Feb 10, 2004
Messages
419
So I was looking on another site which showed how to construct a Period table and join at a higher level than the date so one can make a calculation for month budget vs actual. I already have a fiscal table that is essentially a date column, with 52 fiscal weeks (numbered text), months, qtr with a year calculated in a column. Thing is, no one just does it for a month and I need to do it weekly for week and Qtr eventually recap the year.

However, the fiscal table I have is joined to sales data by date, not period. Of course, I don't want daily budgets to actual, I want to run weekly as a portion of quarterly budgets. I also would think I could create a calculated field to be able to use the date to supply the other fields. I guess what I'm trying to do is not add manually anything to the original data, which is in another spreadsheet on a shared drive. Trying to keep that very clean.

And further, I have to provide multiple measurements. Employees are linked to 6 reports, each has a budget. Right now I can get sales figures no problem all in the same pivot. But now I'm not sure if I could pull off having the budget vs actual right next to the sales data. That would be very nice and compact. Ideas on how to do this? Am I on the right track? What can I put in the budget table to make the quarter and week work in relation to the fiscal table and the sales data, which only has dates.
 
Last edited:

powerpivotpro

Board Regular
Joined
Jan 18, 2012
Messages
242
Your fiscal table has how many rows for a year - 52 or 365? If 365, I don't know how it's joined to your Sales table since that would be "many to many" and therefore an illegal relationship, so I am guessing 52. But that means your Sales table is also NOT day-level, but week-level. Making sure I understand up front.
 

pete234

Active Member
Joined
Feb 10, 2004
Messages
419
Your fiscal table has how many rows for a year - 52 or 365? If 365, I don't know how it's joined to your Sales table since that would be "many to many" and therefore an illegal relationship, so I am guessing 52. But that means your Sales table is also NOT day-level, but week-level. Making sure I understand up front.
365 date rows in the fiscal table to match calendar date to sale-date in the sales reports to enable grouping by Fiscal Week and month --custom. I guess I'd like to preserve this relationship if possible since it works now for totals. So 365 dates, with 52 weeks, qtrs nd year. thx
 

Forum statistics

Threads
1,085,542
Messages
5,384,330
Members
401,887
Latest member
Somesh

Some videos you may like

This Week's Hot Topics

Top