Forum Discussion
Helper IIIComparing headcount budget and actuals
Hi,
I need to be able to compare 2 different datasheets of Headcount budget vs. actuals. The data format is as such:
H/C Budget:
| Budget | 31/01/2019 | 28/02/2019 | 31/03/2019 |
| 2017 Final | XX | XXX | XXX |
| 2018 Provisional | xxx | xxx | xxx |
| 2018 Final | xxx | xxx | xxx |
| etc.. | ... | ... | ... |
In other words, the budget data (for both provisional & final budgets for that year) is organised in date columns going fiorward until future/forecast periods from present day.
The actuals data is a bit different and summarised like so:
| Department | Position | Current Status | Start Date | End date |
| Marketing | Head of Marketing | Established | 31/01/2017 | |
| Operations | Head of Ops | Left | 31/01/2017 | 31/09/2019 |
Hence, to calculate the number of established figures at any one time, I can easily do that. But I need to be able to compare the current established figure to the budgeted figure across different budget types (different years and final/provisional). Also, I can look at this from a forecasting perspective. The actuals data can tell us when a new person is due to join and so I want to be able to calculate and then visualise this forecast data across the different budget sets with the actual data.
How is it best to do so given the above format and to link these datasets together whilst being able to look at the current budget vs. actuals position as well as the forecast position on the monthly basis?
HI tobiasmcbride,
Maybe you can try to create a calculated table to store expand table records of each date of your date ranges and link to your original table. Then you can simply use table fields to summarise values:
Spread revenue across period based on start and end date, slice and dase this using different dates
Regards,
Xiaoxin Sheng
3 Replies
- amitchandak
Super User
The best way is to create common dimensions including a date dimension using the calendar. Now join all these facts(data) with common dimensions and create the required measures.
Refer :https://docs.microsoft.com/en-us/power-bi/guidance/
To get the output shown. Measures on rows. In Matrix visual you have an option "show on rows". Choose that and put dates on column.
In case you need more help, please share sample data and measures calculations you need.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
tobiasmcbrideThe problem I foresee with using a date calendar is that it needs to be continuously updated whereas what we want is it to automatically calculate based on the months across both datasets.
Essentially I need the data in the format I have below to match up.
1. Calculating number of established employees (I have done this)
2. Comparing this against the current month in the budget tab/excel spreadsheet across the provisional and final approved budgets for that particular month.
3. Make sure those can roll forward so that, for instance, for March 2020 the budget can match the actuals in that regard.
HI tobiasmcbride,
Maybe you can try to create a calculated table to store expand table records of each date of your date ranges and link to your original table. Then you can simply use table fields to summarise values:
Spread revenue across period based on start and end date, slice and dase this using different dates
Regards,
Xiaoxin Sheng
