Solved

Period Type in Different Scenarios

  • 1 March 2022
  • 9 replies
  • 816 views

Userlevel 2
Badge +2

Hi Team,
We have been trying to figure out how to visualize and separate actuals and forecast data when sharing rolling forecast scenarios with budget owners and other users, mainly because non-FP&A users are not familiar with the switchover date workflow. We bring this up because the period type is a cross scenario setting, and we set different switchover dates for different scenarios so it won’t help.

What’s the best practice?

 

Roy and the rest from Melio :)

icon

Best answer by francois 7 March 2022, 08:16

View original

9 replies

Userlevel 6
Badge +12

Hi Roy,

I am not sure to understand what you really expect here. When you say “visualize and separate actuals and forecast data” do you mean that you need:

  1. to differentiate the formatting of the cells according to their period type
    or
  2. to add the period type information to your metric?

 

Option 1 - If it is a visual formatting that you expect, for instance, all Actuals with a blue background and Forecast with green, you can apply a manual format on your period type. When your switchover date changes, the format follows. 
Right click on your period type column header, and format cells as you wish.

I recorded a video to show you how to do it.
 


 

Option 2 - if you need your Period type to be displayed, it depends on your metric structure but you can try to add the Period type to the dimensions of the metric using a BY.

 

I hope one of these options answers your questions. If not, do not hesitate to show us a screenshot or an example about what you expect, or alternatively, to submit a support ticket.

Best,

Benoit

 

Userlevel 2
Badge +2

Hey again!

Thats a cool way of visualising it. but the problem is it doesn’t work when you have different switchover date for different scenarios and you compare scenarios.
 

For example when comparing between a scenario that we've set the date to be 03/01/22 and a scenario with a switchover date 02/01/22, Pigment will mark them both as Forecast period type :
 

Is that clearer?

Userlevel 6
Badge +14

Hi Roy,
Thanks for the details on this! In that case, my guess would be to add the Period Type as a dimension of the metric you’re working on, not just as a property of the months (as in Month > Period Type in the Configure panel).
Then to map the correct data to the relevant Period Type, you can create a new metric (format Period Type, dimensions Month, Scenario) like so

IF(Month.'End Date' >= 'Switchover Date by Scenario', 'Period type'.Name."Forecast", 'Period type'.Name."Actual")

Using such a metric, you get an allocation rule for each month on each of your scenarios.

 

Then in your data metric, you can simply add a [BY: -> 'Period Type by Month'] which is equivalent to [BY: 'Period Type by Month', Month] - you can use both, they allow you to add the allocation rule while keeping the Month dimension in your target metric.

 

This is the result I get. I have highlighted the Actuals in green and the Forecast in blue.

Please note that in that case, the Period Type is no longer a property of the Month displayed in the view, but is now part of the metric dimensions. To update this, you’ll have to get to the Structure panel in your metric settings and change the dimensions. Be aware that any data manually input (e.g. using the metric override) will be deleted in that metric, and that you’ll probably have to adapt the calculations that reference this metric by adding a [REMOVE SUM: 'Period Type'] to make sure no hiccups appear further down the line.

Please tell me if this helps!

Userlevel 2
Badge +2

Hi! It does but I don’t understand why Scenario is a metric here?

We use scenarios with this new feature :

 



 

Userlevel 6
Badge +14

Hi Roy,

In the example I’ve provided, the Scenario is indeed a dimension, but you’re right by using native scenarios as they’re much more powerful! 
It doesn’t change much to the solution though.

Scenarios in the configure panel

 

Please tell me if you have further questions.

François

Userlevel 2
Badge +5

While this method is great, I find it difficult to hide empty Actual columns, but keep empty Forecast columns visible.

If I hide empty columns - I don’t have space to enter future forecasts.

If I unhide empty columns - it shows empty Actuals and empty forecasts in the past.

Check out the video:

 

I tried using Filters/Exclude without much luck - the empty columns are still there.

Userlevel 6
Badge +14

Hi Dmitrii,

While it may look similar similar your problem is quite different - in your example, your Actuals and Forecast are two different metrics.

This means there’s little you can do using the methods mentioned above, since you can’t add a Filter metric (that would filter differently on either columns).

To make the columns appear, I would pre-fill at least one of your lines, either with a prediction (e.g. last 12 months + growth), a PREVIOUS(Month) to push the value in November or just zeros.

If you decide to add zeros, be careful of sparsity and try to add values only where you’ll add inputs, for example using IF(ISDEFINED(Actuals[REMOVE: Month]), 0)[FILTER: 'Forecast Month']

If you have other questions, I’d recommend opening a new thread and asking your question there!

Userlevel 2
Badge +5

@francois thanks for the reply.

Then maybe my topic is a bit irrelevant.

I don’t want two metrics Actuals/Forecasts. I merely want to show, which month is actual and which is forecast. Ideally, also implement different coloring (and hopefully access rights) to Actuals.

 

However, I cannot use Scenarios in my Month dimension. Hence my switchover data always stays the same. But if I use `[BY: → ‘Actual? type’]` I cannot see the `Period type` in the Pivot setup for columns. Then I add it as a dimension, which appears to be incorrect and indeed creates two metrics.

 

Can you suggest any tips?

 

What I am trying to repeat is the Anaplan’s functionality of Switchover: when I can see actuals (readonly) and input forecasts - all in the same line (metric).

I’d be happy to hear any tips on this.

Badge +2

Hi @Dmitrii / @Roy Stolero ,

I come late to this topic but below is the solution I’ve been using for several customers so far. That might be useful for you

I have a metric in the datahub Set_Is_Actuals where I specify by Scenario (not the dimension but the real Scenario feature) the last Actuals date. Then, I have a boolean metric (Filter) to know if a month is Actuals or Forecast based on the scenario.

I have several metric using the Period Type and Month dimension, grabbing either Actuals load or Forecast Assumptions. Finally, when I put them in tables, I can use this filter in the table to hide the irrelevant months (Actuals forward months and forecast backward months).

Hope this help,

 

Reply