Previous Year Sales by Store

[majman73]

Hello all,

I'm trying to calculate the previous year sales by store from my data that contains multiple stores over multiple dates.

My raw data is a follows:
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Store_code
[/TD]
[TD]Trade_Year_Week
[/TD]
[TD]retail_value
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]201201
[/TD]
[TD]10.00
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]201201
[/TD]
[TD]15.00
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]201201
[/TD]
[TD]20.00
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]201202
[/TD]
[TD]7.00
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]201202
[/TD]
[TD]9.00
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]201202
[/TD]
[TD]13.00
[/TD]
[/TR]
</TBODY>[/TABLE]

I also have a date table to convert the Trade_Year_Week into dates.

I have created the following measure:

StoreSalesPrevYr:=CALCULATE(Sales_Data[retail_value], DATEADD(Dates[Date], -1, YEAR))

but there is an error stating in the crrent context retail_value can't be calculated.
I assume this is because I have multiple stores for each Trade_Year_Week, so how do I calculte this measure for each store?

Any help would be greatly appreciated.
Thanks

 

[JoeMo]

Copy F2 down to cover all store codes
Sheet1

*	A	B	C	D	E	F
Store_code	Trade_Year_Week	retail_value	*	StoreCode	PriorYrSales
*
*
*
*	*	*
*	*	*
*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:77px;"><col style="width:121px;"><col style="width:81px;"><col style="width:37px;"><col style="width:72px;"><col style="width:81px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]201101[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]201101[/TD]
[TD="align: right"]15[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]201201[/TD]
[TD="align: right"]20[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]201202[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]201202[/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]201102[/TD]
[TD="align: right"]13[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F2 =SUMPRODUCT(($A$2:$A$7=E2)*(VALUE(LEFT($B$2:$B$7,4))=YEAR(TODAY())-1),$C$2:$C$7) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[majman73]

Thanks Joe, but I want to know how to do this in PowerPivot.

Copy F2 down to cover all store codes
Sheet1

*	A	B	C	D	E	F
Store_code	Trade_Year_Week	retail_value	*	StoreCode	PriorYrSales
*
*
*
*	*	*
*	*	*
*	*	*

<TBODY>
[TD="bgcolor: #cacaca"]1
[/TD]

[TD="bgcolor: #cacaca"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]201101
[/TD]
[TD="align: right"]10
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"]10
[/TD]

[TD="bgcolor: #cacaca"]3
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]201101
[/TD]
[TD="align: right"]15
[/TD]

[TD="align: right"]2
[/TD]
[TD="align: right"]15
[/TD]

[TD="bgcolor: #cacaca"]4
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]201201
[/TD]
[TD="align: right"]20
[/TD]

[TD="align: right"]3
[/TD]
[TD="align: right"]13
[/TD]

[TD="bgcolor: #cacaca"]5
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]201202
[/TD]
[TD="align: right"]7
[/TD]

[TD="bgcolor: #cacaca"]6
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]201202
[/TD]
[TD="align: right"]9
[/TD]

[TD="bgcolor: #cacaca"]7
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]201102
[/TD]
[TD="align: right"]13
[/TD]

</TBODY>

Spreadsheet Formulas

Cell Formula F2 =SUMPRODUCT(($A$2:$A$7=E2)*(VALUE(LEFT($B$2:$B$7,4))=YEAR(TODAY())-1),$C$2:$C$7) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4