Solved

Calculating YTD

  • 29 December 2021
  • 2 replies
  • 139 views

Userlevel 1

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.  

icon

Best answer by francois 15 February 2022, 08:38

View original

2 replies

Userlevel 2
Badge +1

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!  

Userlevel 4
Badge +3

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!

Reply