Hi,
I have a table of sales data for the past 3 years.
I have a measure that sums the number of items sold.
I want to covert my pivot table to cube formulas to format a report.
In that report, I need a cell to display the number of items sold for the last two years.
Without using slicers, report filters, or adding/changing a measure* how can I expand the following formulas to sum the number of items sold for the last two years?
Cell A2 = CUBEMEMBER( "PowerPivot Data", "[tblYear].[Calendar Accident Year].&[2013]" )
Cell B1 = CUBEMEMBER( "PowerPivot Data", "[Measures].[Sum of Items Sold]", "Items Sold" )
Cell B2 = CUBEVALUE( "PowerPivot Data", A2, B1 )
I think it should look something like this, but this is not the correct syntax:
= CUBEMEMBER( "PowerPivot Data", "[tblYear].[Calendar Accident Year].&[2013]&[2012]" )
Any help would be greatly appreciated.
*I'd really like to gain a better understanding of how CUBE formulas can be utilized - that is why I am restricting my solution to just modifying the CUBE formula syntax.
Best,
Brandon
I have a table of sales data for the past 3 years.
I have a measure that sums the number of items sold.
I want to covert my pivot table to cube formulas to format a report.
In that report, I need a cell to display the number of items sold for the last two years.
Without using slicers, report filters, or adding/changing a measure* how can I expand the following formulas to sum the number of items sold for the last two years?
Cell A2 = CUBEMEMBER( "PowerPivot Data", "[tblYear].[Calendar Accident Year].&[2013]" )
Cell B1 = CUBEMEMBER( "PowerPivot Data", "[Measures].[Sum of Items Sold]", "Items Sold" )
Cell B2 = CUBEVALUE( "PowerPivot Data", A2, B1 )
I think it should look something like this, but this is not the correct syntax:
= CUBEMEMBER( "PowerPivot Data", "[tblYear].[Calendar Accident Year].&[2013]&[2012]" )
Any help would be greatly appreciated.
*I'd really like to gain a better understanding of how CUBE formulas can be utilized - that is why I am restricting my solution to just modifying the CUBE formula syntax.
Best,
Brandon