Hi,
I'm trying to build YTD formula that is based on switch over data.
For example if the switchover date is November, I would like to sum January-November numbers. What is the easiest way to do that?
I tried to use the YEARTODATE formula, but it's not what I am looking for, as it's adding to each month the sum of the previous months.
Hi Venkata,
About the YTD - if you want to apply your yeartodate() formula until the switchover date, you will need to filter the switchover date's month.
The calculation you would use could look like this:
Yeartodate (‘X Metric - Actuals’) [filter: Month=timedim (‘Switchover date’, Month)]
We filter the month dimension by selecting the switchover date that has been transformed into a month thanks to the timedim() function.
Hope this helps!
Hi Venkata,
One other efficient way to filter out the forecast months would be to change the formula to
Yeartodate (‘X Metric - Actuals’) [filter: Month.'Start Date' <= 'Switchover Date'] That way you’re basing your filter on a Switchover Date metric, which can be later modified to include versions or scenarios.
Please tell me if this works for you!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.