Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
TheG
Frequent Visitor

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 have the data in PBI with the same fields in both tables (FORECASTPF and ACTUALPF) except obviously, one with actual $ values and one with forecasts $ values.

 

I understand creating a calculated table is the best way to join this information for reporting and calculating variances.


Can anyone, advise me of the required steps so i can easily work out the variances for different accounts, jobs and/or periods?

 

I was able to create a measure in my actuals table to calculate the forecast less actuals as shown below:

Forecast v Actual Model.PNG

 

1. But is there a better way to do this and create a single table for reporting on both Fact tables?

2. How do I link the Account Numbers, Names, Cashflow Group and Job number between to two? I am not sure how i should join these too tables to create one for analysis and reporting....?

3. Should i create new tables to hold the account numbers, names, jobs etc? to uniform the selection of these criteria?
Here is my table data so far.

Table StructuresTable Structures

Thanks again in advance for your help!

11 REPLIES 11
v-caliao-msft
Employee
Employee

Hi @TheG,

 

In your scenario, since FORECASTPF table and ACTUALPF table almost have the same table structure, you needn't to use two tables. You can add then into one table with FORECAST and ACTUAL column. Select Combine > Merge Queries from the Home tab on the ribbon.

 

Regards,

Charlie Liao

 

Thanks @v-caliao-msft

 

When i merge i dont get the complete matches probably as some account numbers are not in both tables...i guess i need to rework the table layout and split account numbers out, jobs, account names etc..

 

Merge.PNG

 

 

 

 

Anonymous
Not applicable

As noted above and based on the sample data screenshot, merging Actuals and Forecast could be problematic - you have multiple Actual records for July 2014, but only 1 Forecast record that I can see for that month.

HI @Anonymous yep i think i need to re-organise my tables design.

 

 

Anonymous
Not applicable

If this is more than a once-off analysis, I suggest stepping back briefly and deciding what you want to measure and visualise, and how flexible for future change it needs to be.  Then re-organise tables to suit - e.g. if you're only going to ever want monthly comparisons (not fortnightly, or drill down to individual Actual records), you might consider summarising Actual records to monthly granularity for your measures.  If you do decide on that, Charlie's @v-caliao-msftsuggestion about joining the tables could then make for a simpler model to analyse.

@Anonymous @v-caliao-msft

 

Thanks again guys for input...Yes i need to look at table design...i came up with this but i think the FORECASTPF table needs to be linked via ACCOUNTNUMBER to the ACTUAL table...at the moment i have linked the FORECASTPF transaction date to DATEDIM.

 

I will have other FORECAST excel spreadsheets to add too.

 

Table Treev2.PNG

 

The structure above wont work that well for example if I want to compare the forecast [AccountNumber] against the Actual [AccountNumber]....How should i format the forecast table? The actual table is fine with this setup. I will have other forecast tables as mentioned like FORECASTCC, FORECASTSL, FORECASTEMW, etc....all with similar fields from an excel cashflow as shown in the FORECASTPF table above.

 

here is a sample of FORECAST spreadsheet i am using as the source. I have to manipulate a little to convert top row to headers and then unpivot the date/values.

 

Forecast Example.PNG

 

Currently, FORECASTPF is connected to JOBS (via Jobnumber) and DATEDIM (Calendar Date). I think it needs to be connected to AccountNumber but i cant get that relationship to connect.

Anonymous
Not applicable

Garry,

This is getting more complex with extra columns for Job and Account, and different types of Forecast.  I'm not sure what CC and PF and SL and MW are?

 

From your original model, I agree that linking Account to Forecast seems to make sense.  You'll probably need to use a Cross Filter Direction of Single from your Account table to both Forecast and Actual to avoid an 'ambiguous relationship' error.  And the same may be required from Jobs as well.

 

Do you want to analyse Actual vs Forecast by both Job AND Account?

 

 

@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
Not applicable

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.

Anonymous
Not applicable

In some cases, you might prefer to keep them separate, at least as staging tables - e.g. update scenarios where you're adding new Actuals each month, checking in case Actuals for a new Account Number or Job Number have been added but no Forecast exists for it etc.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors