Actual vs Budget: multiple measurements, periods

[pete234]

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.

 

[powerpivotpro]

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]

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