I asked this on the RS BI board, but thought maybe it should go here
Source: a TM1 cube
Problem: trying to dynamically display 3 years of data based on the current fiscal year on a Report Studio Report
Description: I have a time dimension (Fiscal_Period) that has the following rollup member: Fiscal Periods, YTD, Forecastable Periods. Within those members are the years, so Fiscal Periods has 2015, 2016, 2017, 2018. The dimension (Fiscal_Period) also has attributes attached to each period one of them being "is_current_year". Our fiscal year end is February. So in Jan/16 and Feb/16 the current fiscal year is 2016 and in March/16 onward its 2017.
We want the report to show CY, CY+1 and CY+2 data in the columns but don't want to have to go in and change the report every year, so we added the "is_current_year" attribute.
I can get the CY to pick up the right year using the following statement: filter (children([Fiscal Periods]),[TM1 ExpCt].[Fiscal_Period].[Fiscal_Period].[is_current_year]='Y' and [TM1 ExpCt].[Fiscal_Period].[Fiscal_Period].[level_num] = 2), but from there I can't figure out how to get CY+1 and CY+2. Tried nextMember but it doesn't work, that's where I get the coercion error.
Thoughts?