year-to-date for past years

I'm trying to formulate a report where it shows year-to-date actuals for the current year, and also where our actuals were on the exact same day for previous years. For example, today is January 15th, and I want the report to show:

Fiscal Year 2017: actuals from start of fiscal year to January 15th of fiscal year 2017

Fiscal Year 2018: actuals from start of fiscal year to January 15th of fiscal year 2018

Fiscal Year 2019: actuals from start of fiscal year to January 15th of this FY

I imagine this would include a time function in the layout such as "Yearly Cumulative Value" or something like this, but nothing I have tried has worked. Any ideas? Thanks!

Find more posts tagged with

Accepted answers

All comments

Previous Member

Hi Todd Andersen,

have you tried combining the "Yearly cumulative value" with the period offset (-12 for previous year, -24 for the year before that)?

If you have the monthly values row by row, then you could also try using the ranking function "cumulated value". See Ranking function.

BR, Ray

unknown

I tried that and it did not work either. Below is what I entered into the time function on our "Imported Actuals" cube, which is dimensioned by month. Whatever I try, the previous fiscal year actuals will always show the total for the whole fiscal year, and not the specified year-to-date period.

Previous Member

I just noticed you are giving the 15th of January in the example. Do you use the daily level on the calendar? And if so, are your cubes versioned on day level instead of month level? If that happens to be the case, the period offset might be an offset of days instead of months.

We don't have a separate fiscal year, so I'm not sure if using the regular year and modifying the period offset accordingly would do the trick. But I'd wager you have tried that too - sounds like a case for the support team.