Solved

Period Type in Different Scenarios

  • 1 March 2022
  • 5 replies
  • 235 views

Userlevel 2
Badge

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

5 replies

Userlevel 3
Badge +2

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

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 4
Badge +3

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

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

We use scenarios with this new feature :

 



 

Userlevel 4
Badge +3

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

Reply