Cannot display measure variance in Excel pivot table

Question

I'm trying to display the difference in a measure from one day to the next in Excel (2007) by setting the value field setting to 'Difference From' with Base field = day and Base item = previous. If my underlying data is a SSAS cube, the value that displays for each cell is #N/A. If my underlying data is a set of records on another worksheet (and I use it to create a pivot table), the value displays correctly. Both data sets are the same.

Do I need to create a calculated cube field or am I missing something?

Thanks in advance.

Answer 1

I ended up using a calculated cube field that looks like:

([Current Day].[Day].CurrentMember, [Measures].[Volume]) - ([Current Day].[Day].CurrentMember.Lag(1), [Measures].[Volume])