Solved

Modeling by version (Budget and Forecast)

  • 16 June 2022
  • 1 reply
  • 386 views
C
Rank
Userlevel 3
Badge

I am modeling our people expenses but I will need that for other modeling. I want to prepare it by version (budget, forecast 1, forecast 2, etc) and I want to let it easier to switch between versions as we need.

For that, I am creating thoose metrics:

  • Metric 1: Headcount Starting Point: that is the number of headcount we have just before starting planning the budget or forecast: (configured by: Version, month, cost center and seniority)
  • Metric 2: Headcount addition: that will be where we will input the number of new headcount we are planning to have (configured by: Version, month, cost center and seniority)
  • Metric 3: Total Headcount: Is the sum of  metrics 1 and 2 that gives me the final number of headcount (configured by: Version, month, cost center and seniority)

My question is: How can I make sure that metric 3 only picks up data for the appropriate version? 

And also, how can I change the version it picks up accordingly with our planning time? For example: In November 22 we are going to do Budget 2023; in March 23 we are going to do Forecast 1,  in June 23 we are going to do Forecast 2, etc...

icon

Best answer by francois 16 June 2022, 08:59

View original

1 reply

francois
Rank
Userlevel 6
Badge +14

Hi Carolina,

If Metric 1 and Metric 2 are by Version, data stored on a Version will appear on that specific Version in Metric 3. Same goes for data stored at the Cost-Center, Month or Seniority granularity!

On your second question, it looks like you’re looking for a rolling forecast. If you only have one switchover date in your dataset, you can activate Enable differentiating your data between actual and forecast from the calendar settings and use a formula like IF(Month.'Period Type' = 'Period Type'."Actual", 'Metric'[SELECT: Version."Actual"], 'Metric'[SELECT: Version."Forecast 1"]). Using SELECT filters the data on the specific item, and removes the dimension from the result. To learn more about it, try and input 'Metric' alone and 'Metric'[SELECT: Version."Forecast 1"] (while replacing the relevant names with your metric and dimension names) in a Formula Playground, and observe the data and the dimensions.

If you want to go a bit further, I’d suggest reading this article on Switchover Date and this question on reporting different period types

François (Pigment)

Reply


Ask your question here!

Terms & ConditionsCookie settings