Skip to main content

Hi Pigment Community,

we have a use case in a project where we need to combine 2 special cases in the area of Switchover dates.
 

  1. We have multiple switchover dates for multiple data sources, as some supplier report only monthly and some other quarterly. So we need our Forecast calculation to start at different months for different data.
  2. We want to auto calculate the switchover date, by the last available data in the Actuals data set. I haven’t found a solution on how to automatically calculate the last Actuals month.

 

For 1. I just created a multiple dimension metric with switchover dates for all data layers, which works fine for manually setting the month for the switchover dates.

I then created second metric to calculate the last month with Actual data and this formula gives me the output for the last available month:

'ACT_REV_Euro (in k)' kREMOVE LASTNONBLANK: Month]

 

However, I was not able to transform or extract the Month value from the metric. Any formula like “MONTH()” or “TIMEDIM()” seems not to work at all.

I am happy for every idea that helps.

Best, Marco

Hi Marco,

 

REMOVE is removing a dimension from the source. You need your source to output the Month itself.

Do like this:

IFDEFINED('ACT_REV_Euro (in k)' ,Month))REMOVE LASTNONBLANK:Month]

*you could do Month.’Start Date’ if you wanted a date.

 

Run this in the playground step by step so you can see how it works

 

Thanks for the very fast response! It worked fine.

Edit: I fixed it to my needs.

​@MARCO_BMG Please try the formula below to get the start date

IFDEFINED('ACT_REV_Euro (in k)' ,Month)tREMOVE LASTNONBLANK:Month].'Start Date'
or

IFDEFINED('ACT_REV_Euro (in k)' ,Month.'Start Date')eREMOVE LASTNONBLANK:Month]

 

Reply


Ask your question here!

Terms & ConditionsCookie settings