PowerPivot Year on Year values

[MartinL]

Having found what I thought was the perfect solution to my massive workbooks, I find I am having headaches trying to produce YoY reports.

In the old system I had links to 2 workbooks covering a range of 28 columns x 300000 rows
over this I had around 15 pivot tables, charts & VBA

However this made the file size 150,000kb and with the Excel (and windows) overhead I was upto 3.5GB of memory without having outlook or anything else open

Using PowerPivot over a single csv file instead has reduced my file size to 10,000kb although there is only the one pivot table and no charts or VBA yet in this.

OK pontificating over, this is my dilema

My Dataset covers Jan 2009 to date and grows every week
My powerpivot looks like this:
Sheet1

*	A	B	C	D	E
1	Geography	Barcode	Value Sales	Store Numeric Distribution	Date
2	RetailerA	852180	85212	625	10/01/2009
3	RetailerA	841139	76883	587	10/01/2009
4	RetailerA	147217	58077	467	10/01/2009

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 108px;"><col style="width: 84px;"><col style="width: 85px;"><col style="width: 176px;"><col style="width: 75px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

What I want to be able to do is somewhere in my workbook have an end date entered, and have the DAX powerpivot pickit up inorder to create a 4 week, 13 week, 26 week and 52 week set of values and also the values in the same period of the previous year.

What I used to do is have a table that calculated the dates thus:
Period
An end date is entered into E5

*	C	D	E
4	*	Start	End
5	52WTY	14 June 2011	05 June 2012
6	52WLY	15 June 2010	07 June 2011
7	26WTY	13 December 2011	05 June 2012
8	26WLY	14 December 2010	07 June 2011
9	13WTY	13 March 2012	05 June 2012
10	13WLY	15 March 2011	07 June 2011
11	4WTY	15 May 2012	05 June 2012
12	4WLY	17 May 2011	07 June 2011

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 126px;"><col style="width: 126px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D5 =E5-(51*7) D6 =E6-(51*7) E6 =D5-7 D7 =E7-(25*7) E7 =E5 D8 =E8-(25*7) E8 =E6 D9 =E9-(12*7) E9 =E5 D10 =E10-(12*7) E10 =E6 D11 =E11-(3*7) E11 =E5 D12 =E12-(3*7) E12 =E6 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I added 4 calculated columns called 4, 12, 26, 52
which used this information to set "TY" or "LY" against each line, thus

EPOS

*	S
2549	*
2550	LY

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula S2549 =IF(AND([@Date]>=Period!$D$5,[@Date]<=Period!$E$5),"TY",IF(AND([@Date]>=Period!$D$6,[@Date]<=Period!$E$6),"LY","")) S2550 =IF(AND([@Date]>=Period!$D$5,[@Date]<=Period!$E$5),"TY",IF(AND([@Date]>=Period!$D$6,[@Date]<=Period!$E$6),"LY","")) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Can anyone tell me how I do this in powerpivot, maybe using DATESBETWEEN or something.

Martin

 

[MartinL]

Mike

that is so close

the calculations work fine, the weeks do not at this stage need to by dynamic, although this may change as people get more anal.
and if possible show zeros or blanks where uplift is negative

I am going to work on that tomorrow

Mike I owe you a drink at your fav watering hole!

Martin

 

[MartinL]

that was easy

=if(CALCULATE('Total Data Dump'[Prize]-'Total Data Dump'[Total Sales],Dates

#VALUE!

#VALUE!

 

[masplin]

I like a challenge and I learnt something. I think the next step to remove the extra columns might be to convert the whole thing to CUBEFORMULAE which I've never used.

 

[MartinL]

Hmmm, I haven't even seen that one, for now I just hid the columns.

I will look into that one

Martin