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.
- 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.
- 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