Actuals vs Forecast - Months as columns

Hi,
I’m about to start some work for our project team. It’s essentially doing time intelligence with filters by Project name or CIO. The issue I have is that the file they provided me has the actuals and forecast as columns. It seems odd to have the data in this format. If there a better data model setup that i should be using so that i can properly do time intelligence.

I’ve attached a mini data sample (excel) and PBX for review
EDNA - Forecats vs Actual.pbix (35.7 KB)
Dataset - Actuals vs Forecast.xlsx (11.6 KB)

1 Like

Hi Chad,

In Power Query, I went through the following steps

  • Select the first 4 columns

  • Select Unpivoted other columns

  • Select the attribute column and TRIM the text as it has leading spaces and some trailing spaces

  • Split the attribute column by delimitter of a space to split the date into two columns to create a column for Actuals/Forecasts and the date now appears in a new column

  • Split the date column by delimitter / to split the date into three columns

  • Select the three “new” date columns and select them in order of how you wish to reconstruct them, so day/month/year (if using UK date format)

  • Merge these columns

  • Change column type to date

The data is now in a tabular format so you can utilise time intelligence once you connect a date table.

One issue with your source data, a couple of the date headers are missing / so it I tidied that up before pasting your data into Power BI

EDNA - Forecats vs Actual.pbix (33.1 KB)

2 Likes

That really doesn’t matter much how it shows because you can make any of those columns the axis or the legend if you are using a stacked graph as an example. Sure you could switch the data as well or just put the actuals and forecast in another tab as rows instead.

Oh my… this is great!
Exactly what I was looking for… I’ll practice this model going forward so that I see how you did it.

2 Likes

@chad.sharpe ah cool, great that it works for you.

Just check out the headers which have the / missing between the months & years in your Excel file in cells R1 & X1.

Unpivoting is awesome, by using Unpivot Other Columns it will ensure any futher columns added to the end will also get “flipped” too.

1 Like

I’m going to play with the unpivot now… it’s pretty cool…

The one good thing about the datafile is that they do not add columns throughout the year. As you can see some of the future months have ZERO data in them. All that happens on a monthly bases is that they will update these columns with data.

This unpivot works perfect!.

1 Like

Cool, nice & easy from here on in then! :man_dancing:

1 Like

Nice to see that you found the answer you are looking for @chad.sharpe

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,We hope you’ll give your insights on how we can further improve the Support forum. Thanks!

I spoke to soon… hopefully you can take a peak at this question as I’m stumped with how to address some other data points in the data set. The unpivot works great for the forecast/actuals. But aside from there there are other columns that I need to sum up or create measures around. Once i unpivot the table it creates all the new rows. Which in-turn makes the summing of other columns incorrect… how do I address this? I was going to potentially create two Tables, once for the forecast/actuals (which is the only data that is driven by (DATE). Then have a seperate table linked by the Project Name with all the other data points?

Is there another easyier way to create measures or calculated columns from the unpivoted table?
Dataset - Actuals vs Forecast (updated with expense).xlsx (18.1 KB)
EDNA - Forecats vs Actual (New Column Added).pbix (41.1 KB)

1 Like

First, can I assume “Barry No Hall of Fame” CIO is a reference to Barry Bonds? If so, I agree whole heartedly :slight_smile:

I’ve been working thru a data model for my financials and this is what I’ve done:

Our ERP stores Actuals and Budgets in separate tables, each with 24 columns (DEBITS & CREDITS for 12 months) and a row for each fiscal year. Forecast is a function of YTD Actuals and Remaining Budget, so that is derived.

The first thing I did was net the 24 debit/credit pairs to twelve columns I call AMOUNT01, AMOUNT02, …AMOUNT12 (Jan thru Dec). After that, I pivot the 12 amount columns into rows, assigning the last day of the month for each column as the date.

I’ve decided to store actuals, budgets, and forecasts in the same table, using a column I’m calling AMOUNT_CLASS to identify whether it’s actual, budget, or forecast. I then created a measure I’m calling “Raw Amounts” that does a simple sum of my AMOUNT column. This turns one row of Actuals with GL_KEY and FISCAL_YEAR columns plus the 12 amount columns into 12 rows of ACTUALS with a GL_KEY, FISCAL_YEAR, DATE, and 1 AMOUNT column. I also create a column I call AMOUNT_CLASS and assign it a value of ACTUALS. I then do the same thing for BUDGET, and then derive FORECAST. I changed your Dataset file and am attaching it as a reference to what I’m describing. I wasn’t sure what the Planned Expense, Unknown Expense, and Answer columns were for, so I left them alone.
chad-data-example.xlsx (12.4 KB)

After I import my data, I then build a simple measure I call “Raw Amount” which is just a summary of the AMOUNT column. From there, I branch off and create measures for Actuals, Budget, and Forecast.

1 Like

Thanks… this is very helpful. And yes, it is Barry Bonds :slight_smile:

2 Likes