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
Geography	Barcode	Value Sales	Store Numeric Distribution	Date
RetailerA
RetailerA
RetailerA

<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>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: right"]852180[/TD]
[TD="align: right"]85212[/TD]
[TD="align: right"]625[/TD]
[TD="align: right"]10/01/2009[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="align: right"]841139[/TD]
[TD="align: right"]76883[/TD]
[TD="align: right"]587[/TD]
[TD="align: right"]10/01/2009[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: right"]147217[/TD]
[TD="align: right"]58077[/TD]
[TD="align: right"]467[/TD]
[TD="align: right"]10/01/2009[/TD]

</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
52WTY
52WLY
26WTY
26WLY
13WTY
13WLY
4WTY
4WLY

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 126px;"><col style="width: 126px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]End[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: center"]14 June 2011[/TD]
[TD="bgcolor: #FFFFCC, align: center"]05 June 2012[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: center"]15 June 2010[/TD]
[TD="align: center"]07 June 2011[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="align: center"]13 December 2011[/TD]
[TD="align: center"]05 June 2012[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="align: center"]14 December 2010[/TD]
[TD="align: center"]07 June 2011[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

[TD="align: center"]13 March 2012[/TD]
[TD="align: center"]05 June 2012[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="align: center"]15 March 2011[/TD]
[TD="align: center"]07 June 2011[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

[TD="align: center"]15 May 2012[/TD]
[TD="align: center"]05 June 2012[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]

[TD="align: center"]17 May 2011[/TD]
[TD="align: center"]07 June 2011[/TD]

</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
*
LY

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]2549[/TD]

[TD="bgcolor: #CACACA, align: center"]2550[/TD]

</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