Hi Declan,

It's great to hear you're making use of Power Pivot.

Power Pivot course session 6.07 deals with this. You can keep your 3 fact tables, but you'll also need a dimension table for the Dates. You can create 3 explicit measures that sum the actual, budget and forecast. Then you can create further explicit measures that calculate the variances which reference the previous 3 explicit measures.

I hope that points you in the right direction, but shout if you get stuck.

Mynda

Thanks , the training is fantastic, its just translating it into my versions that is a bit more challenging bu I will get there! 

From your experience given your financial services experiences, is it best to have 3 fact tables, is that how you would have set it up? and does it need a column to state if its actual, budget or forecast? 

Sorry for all the queries, but really want to learn the right way with this rather than the workarounds I normally end up doing. 

Hi Declan,

If all 3 tables only store the values at the first of each month, then it would be a smaller file if you created one table with the following columns:

- Date

- General Ledger Code

- Cost Centre Code

- Actual 

- Budget

- Forecast

You can use Power Query to merge the Budget and Forecast figures into the Actual table, which you then load into Power Pivot. Note: you will need to use the Full Outer join because the Actual table won't necessarily contain rows for all Date, GL code and Cost Centre combinations.

Mynda

Thanks Mynda, will give this a try. 

Now I am trying this I am encountering an issue, and may not have explained fully. Calendar year runs from April to March. I dont have budget and forecast data for prior year (April 2019 to March 2020) as only interested in actual vs actual for prior year comparison. So Actuals are from April 2019 and then continue as these become available at each month end. 

Because of this , when I use actual as the base and then merge forecast and budget, actual has dates from prior year up to current month whereas budget and forecast only have current month and rest of financial year months. so when I merge it only pulls in budget for current month but get all actuals from April 2019 to date. 

When I do it the other way with budget or forecast as base, then I have the issue that it doesnt bring in previous year for actuals. 

Is the single table structure better from an ability to analyze/create further detailed pivots, or will the 3 table structure work as well? (As I have begun to write measures based on 3 table. The data will never be millions of rows, its a fairly simple company and its only high level GL data not lowest level transactional, so my priority is more ability to analyse and create pivots for dashboards as opposed to ability to manage and process large data sets.

Thanks again as always

Hi Declan,

If you use the "Full Outer Join" (mentioned in the note above) it will bring in all rows from both tables. It will be easier to write the measures if the data is in one table, but only marginally as you will still need a date/calendar table to use the Time Intelligence functions.

Mynda

Hi, I thought I had selected full outer join, but had clicked on right outer by mistake.

I have tried this but getting errors, I think this is because when I analyse the data from actuals not everything is on the 1st of the month, so will be different days in the month, whereas budget and forecast will always just be defaulted to 1st of the month. I have still tried this under the assumption that comparing month on month will still work but it doesnt balance. Is this as you would expect because of the different days or is the error still something else I am doing wrong?

I have created the date/calendar table and have been making really good progress with some elements, as I need to use Prior Month, Prior Year and YTD functions, and have got them working with the 3 tables, but still with some issues and snags, which is why I am trying to see if this single table is still the way I need to go to resolve those 

THanks

Hi Declan,

It shouldn't matter if the actuals aren't all on the first of the month. Your one big table might have budget and forecast on the 1st and then actuals on the 10th, so two rows of data for the month rather than one, but the PivotTable will just aggregate them to the month level anyway.

If you're getting errors then that's another issue. If you want to share your file with me I can take a look. Either upload it here, or if it has confidential data that you can't easily remove, you can email it to me: website at MyOnlineTrainingHub.com

Mynda