Skip to main content

When I follow the training materials and the training videos provided, I have to create the FORECASTED REVENUE metric. When I enter the formula, it accepts the syntax of the formula (no errors), but does not show data in the forecasted months (Oct - Dec).

Formula I use:

IF('Period type'.Name."Actual", Revenue[REMOVE:Team, Account], PREVIOUS(Month)*(1 + 'Growth Assumptions')).

Data for Actual month are populated like the video example, but there is no data. And the Growth Assumptions is populated with the growth percentages.

 

Hi Jan,

In Pigment, you need to be sure the dimensions of your metrics are aligned and if they aren’t tell Pigment how to map them. You should try to type this formula in the Playground (bottom left) and see if the dimensions it “creates” are the same as those you expect.

a few questions:

  • Growth Assumptions: what are the dimensions of this metric ?
  • Same for Revenue

If you answer this, we can help aligning your formula.

 

Nathan


Hi Nathan,

I tried the Formula Playground, but creating the formula using PREVIOUS gives this error: “Error: Function error: PREVIOUS function not supported in the Formula Playground”

Answer to your questions:

  • Growth Assumptions: has the Scenario dimension
  • Revenue:  has the dimensions Country, Month, Team, Account

Regards, Jan


Hello Jan, 

I believe the syntax is more: 
IF(Month.’Period type’ =  ‘Period type’.“Actual”, RevenuetREMOVE:Team, Account], PREVIOUS(Month)*(1 + 'Growth Assumptions'))

And even then, Nathan is right, the dimensions of your growth assumptions need to be aligned with the revenue (at least one dimension in common). So you probably need to add the scenario dimension to your revenue metric (or use the “add” modifier in your formula) such as : Revenue REMOVE:Team, Account]eadd: scenarios]

Ben


Hi Jan,

Another solution would be to use the presence of data itself as condition for the previous, e.g. IF(ISDEFINED(Revenue[REMOVE:Team, Account]) …

François


Hi Ben,

The first part of the IF-statement seems to work fine, also looking at the numbers my model produces compared to the video. Adding Scenario [ADD: Scenario] does not make a difference (same output) and does not make sense in my opinion, Pigment knows that metric Revenue does not have Scenario, so it populates the Revenue to all three Scenarios (same number per Scenario) in a 1 → N relation.

The second part does not the expected results. Formula is from a syntax perspective correct, but no values are returned. It should, for Forecast month, look to previous months and populate the Sep data to Oct - Dec. That is not the case.

Hope this helps to explain in more detail.

Jan  


If you put your formula in the formula playground (without the “previous”) you could see the first part of your if statement is not correct though and does not align with the dimensions you put in the structure of your metric. The condition of your “if” is solely based on the “Period Type” dimension which is not present in the rest of your if statement. 
 


Hi Ben,

Maybe I’m missing an essential point here, but as I understood yesterday, the syntax has changed within the Pigment platform. Your formula (without the PREVIOUS) does generate an error in the Formula Playground. My formula adjusted (without the PREVIOUS), does generate the exact same results as shown in the training video. So I assume this part of the formula is correct:

IF('Period type'.Name."Actual", RevenuenREMOVE:Team, Account], 1)

Once I start to enter the FALSE condition (using PREVIOUS), the formula seems to be correct (no errors), but empty cells for Oct - Dec.

Jan


Jan, 
 

I am not sure what is the problem you encounter, I can assure you the syntax I proposed is accurate. Please look at the example below: 
 


And in custom mode, you can even use the previous function in the playground as well:
 

Ben


YESSSS!!

Indeed Ben, the first part did the trick! My new formula (changes in red):
IF(Month.'Period type'='Period type'."Actual", Revenue[REMOVE:Team, Account], PREVIOUS(Month)*(1 + 'Growth Assumptions'))

Now it shows the forecasted data for Oct - Dec!

Also good to know the Custom setting in Formula Playground, that is really helpful.

Many thanks!

Jan


Reply