Creating a Dynamic Named set in an Olap Pivot Table

[libby845]

Hello -

I have an OLAP pivot. I created a named set for columns using the named set builder in excel. I built the set because I can't easily cross years using an OLAP Pivot because I need to pull different Measures in the columns (messy).

Column 1 Dec Prior Year for Measure.Period End balance.
Column 2 - 12 Jan - Dec for Measure.Amount
Column 13 Dec current year Measure.Period End Balance.

I used the build named set function in excel and got this (This is the MDX auto-built).

{([FISCALYEARS].[FISCAL YEAR].&[2015],[PERIODS].[PERIOD DESCRIPTION].&[12],[Measures].[PERIOD END BALANCE]),([FISCALYEARS].[FISCAL YEAR].&[2016],[PERIODS].[PERIOD DESCRIPTION].&[1],[Measures].[AMOUNT]),([FISCALYEARS].[FISCAL YEAR].&[2016],[PERIODS].[PERIOD DESCRIPTION].&[2],[Measures].[AMOUNT]),([FISCALYEARS].[FISCAL YEAR].&[2016],[PERIODS].[PERIOD DESCRIPTION].&[3],[Measures].[AMOUNT]),([FISCALYEARS].[FISCAL YEAR].&[2016],[PERIODS].[PERIOD DESCRIPTION].&[4],[Measures].[AMOUNT]),([FISCALYEARS].[FISCAL YEAR].&[2016],[PERIODS].[PERIOD DESCRIPTION].&[5],[Measures].[AMOUNT]),([FISCALYEARS].[FISCAL YEAR].&[2016],[PERIODS].[PERIOD DESCRIPTION].&[6],[Measures].[AMOUNT]),([FISCALYEARS].[FISCAL YEAR].&[2016],[PERIODS].[PERIOD DESCRIPTION].&[7],[Measures].[AMOUNT]),([FISCALYEARS].[FISCAL YEAR].&[2016],[PERIODS].[PERIOD DESCRIPTION].&[8],[Measures].[AMOUNT]),([FISCALYEARS].[FISCAL YEAR].&[2016],[PERIODS].[PERIOD DESCRIPTION].&[9],[Measures].[AMOUNT]),([FISCALYEARS].[FISCAL YEAR].&[2016],[PERIODS].[PERIOD DESCRIPTION].&[10],[Measures].[AMOUNT]),([FISCALYEARS].[FISCAL YEAR].&[2016],[PERIODS].[PERIOD DESCRIPTION].&[11],[Measures].[AMOUNT]),([FISCALYEARS].[FISCAL YEAR].&[2016],[PERIODS].[PERIOD DESCRIPTION].&[12],[Measures].[AMOUNT]),([FISCALYEARS].[FISCAL YEAR].&[2016],[PERIODS].[PERIOD DESCRIPTION].&[12],[Measures].[PERIOD END BALANCE])}

This works as expected. However the year is hard coded. Is there a way I can point the Fiscal Year to the report filter for current year and (report filter - 1) for prior year?

Thanks!
Pam

 

[SpillerBD]

I think you need a text value in place of the [2016]
So maybe

Text(Year(Today()),"[YYYY]"

Are you missing out on PowerPivot?
Rob Collie mentioned in his book about building custom calendars. Also at his PowerPivotPro site.
...and no I'm not in the OLAP group