Forum Discussion

TheG's avatar
TheG
Icon for Advocate I rankAdvocate I
9 years ago

Forecast V Actual Comparison Table

Hi Community,   I was looking for the best practice when deciding how to report on Actuals (from an accounting system exported into Excel) and Forecasts (written in excel spreadsheet system).   I...
Need Help
Tutorial Requests
TheG's avatar
TheG
Icon for Advocate I rankAdvocate I
9 years ago

Anonymous

 

 

Hey steve, the forecast table is made up of Professional Fees (PF), Constructions Costs (CC), etc its for a property development. It will be in one spreadsheet actually but i split it for reporting purposes...The accountantlinkcode will indicate the group.

 

I am thinking I could merge the FORECAST and ACTUAL table into 1 fact table as advised before...The only unique fields in the forecast is the cashflow description (which i can do without if i can get the account name), transaction date and Value.

 

Yes there is a requirement for different jobs and account ranges...

 

I guess i have two fact tables ACCOUNTS from accounting system and FORECAST from Excel which have similar fields.

 

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Given that Actuals (not "ACCOUNTS"?) from the accounting system and Forecast from Excel are imported separately, I'd suggest you start wiith:

  • Import Forecast and Actuals as separate fact tables
  • Extract out unique Account and Job dimensions, and create your Date dimension table as you've alrady done
  • Create relationships from your 3 x dimension lookup tables down to Actuals and Forecast, using "Single" for Cross Filter Direction as needed to avoid ambiguity
  • Use the FiscalMonth column on your Date table in measures to join related Actuals with their monthly Forecast

 You could collapse it all into just a couple of big table.  But doing the above steps up front should give you the foundations of a readable, maintainable model from which to most simply create measures and reports. There's also a good discussion by Matt Allington on creating Lookup/Dimension tables per above at http://exceleratorbi.com.au/create-lookup-table-power-pivot/ thats work a read.

 

Cheers,

Steve.