Pivottable Calculated Item alternative

[CharlesHall]

Because of the way my data is structured, I have used pivottable calculated items to solve some row level calculations - such as finding the difference between two rows on the pivot table. This has been easily accomplished by creating a calculated item and adding it as another row on the table. However, when moving this to PowerPivot, calculated items is not available. I suspect MDX Named Sets would be the solution, but I can not figure out the syntax to create a new row that is a combination of other existing sets. Is MDX the right solution to this problem vs DAX, and if so, can you help with the syntax? Thanks

 

[Mavericks334]

Could you paste a copy of your raw data, based on that we could see how to create a syntax for you

 

[CharlesHall]

Could you paste a copy of your raw data, based on that we could see how to create a syntax for you

Here is the raw data - I have simplified it - each of these rows has a unique key but in the pivot table I present just Cur Yr and Last Yr by week

So I would like to create a pivottable row that is the difference between Cur Yr and Last Yr

Measure	Reporting Year	Wk 01	Wk 02	Wk 03	Wk 04	Wk 05
Dollars	Last Yr	6595.74	8749.82	6581.77	5798.34	6552.07
Dollars	Last Yr	634.26	603.37	571.74	672.89	470.75
Dollars	Last Yr	385.29	342.38	386.25	361.65	347.26
Dollars	Cur Yr	15641	18749.4	21936	23840.9	29076.4
Dollars	Cur Yr	5366.26	6142.39	6097	5611.11	5965.52
Dollars	Cur Yr	2139.7	2140.02	2690.97	2758.74	2673.64
Dollars	Cur Yr	54946.4	57312.1	62800.3	64286.9	67568.1
Dollars	Cur Yr	5388.2	5828.68	5945.28	6070.63	6272.98
Dollars	Cur Yr	30023.2	29213.7	54118	51543.6	35183.4

<tbody>
</tbody>

Thanks

 

[jrg24]

Nevermind....I tried to figure a solution but I keep finding flaws with it. I have to deal with data formatted a similar way and it drives me crazy.

 

[Mavericks334]

if your raw data has dates i.e 11/5/2012,11/6/2012, from which you are creating the week. All you need to do is create a tables with dates and Year periods, from previous years, and create a relationship to your data range in raw data.

Create Measures,

Value_Corrected = Sum(Dollars)

YTD_ Sum=CALCULATE([Value_Corrected],DATESBETWEEN(Dates[Date],DATEADD(LASTDATE(Year_Period[Month_Start_Date]),MAX(Year_Period[Fiscal_Period])*-1,MONTH),LASTDATE(Year_Period[Month_End_Date])))

Previous_YTD_SUM =CALCULATE([Value_Corrected],DATESBETWEEN(Dates[Date],Year_Period[Selected_PY_Year_P_End_Date],Year_Period[Selected_PY_Month_End_Date]))


Selected_PY_Year_P_End_Date = lastdate(dateadd(Year_Period[Month_End_Date],-2,YEAR))
Selected_PY_Month_End_Date = lastdate(dateadd(Year_Period[Month_End_Date],-1,YEAR))

This is how your year_period Table should look like.
[TABLE="width: 866"]
<COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 6326" width=173><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7387" span=2 width=202><TBODY>[TR]
[TD="width: 80"]Year_End[/TD]
[TD="width: 104"]Fiscal_Period[/TD]
[TD="width: 125"]Calendar_Period[/TD]
[TD="class: xl63, width: 130"]Month_End_Date[/TD]
[TD="class: xl63, width: 136"]Month_Start_Date[/TD]
[TD="class: xl63, width: 173"]Next_Month_Start_Date[/TD]
[TD="class: xl63, width: 202"]Previous_Month_End_Date[/TD]
[TD="class: xl63, width: 202"]Previous_Month_Start_Date[/TD]
[/TR]
[TR]
[TD="align: right"]2008[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="class: xl63, align: right"]10/31/2007[/TD]
[TD="class: xl63, align: right"]10/1/2007[/TD]
[TD="class: xl63, align: right"]11/1/2007[/TD]
[TD="class: xl63, align: right"]9/30/2007[/TD]
[TD="class: xl63, align: right"]9/1/2007[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2008[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11/30/2007[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11/1/2007[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12/1/2007[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10/31/2007[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10/1/2007[/TD]
[/TR]
[TR]
[TD="align: right"]2008[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12[/TD]
[TD="class: xl63, align: right"]12/31/2007[/TD]
[TD="class: xl63, align: right"]12/1/2007[/TD]
[TD="class: xl63, align: right"]1/1/2008[/TD]
[TD="class: xl63, align: right"]11/30/2007[/TD]
[TD="class: xl63, align: right"]11/1/2007[/TD]
[/TR]
</TBODY>[/TABLE]


Dates Table:

[TABLE="width: 56"]
<COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY>[TR]
[TD="class: xl65, width: 75"]Date[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]7/1/2008[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]7/2/2008[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]7/3/2008[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]7/4/2008[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]7/5/2008[/TD]
[/TR]
</TBODY>[/TABLE]