# PowerPivot Year on Year values



## MartinL (Jun 15, 2012)

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*


*ABCDE1GeographyBarcodeValue SalesStore Numeric DistributionDate2RetailerA8521808521262510/01/20093RetailerA8411397688358710/01/20094RetailerA1472175807746710/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*E4*StartEnd552WTY14 June 201105 June 2012652WLY15 June 201007 June 2011726WTY13 December 201105 June 2012826WLY14 December 201007 June 2011913WTY13 March 201205 June 20121013WLY15 March 201107 June 2011114WTY15 May 201205 June 2012124WLY17 May 201107 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*
CellFormulaD5=E5-(51*7)D6=E6-(51*7)E6=D5-7D7=E7-(25*7)E7=E5D8=E8-(25*7)E8=E6D9=E9-(12*7)E9=E5D10=E10-(12*7)E10=E6D11=E11-(3*7)E11=E5D12=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*


*S2549*2550LY

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

</tbody>

*Spreadsheet Formulas*
CellFormulaS2549=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


----------



## masplin (Jun 16, 2012)

Hi Martin 

Thought I would have a go at this as I'm also working on a mssive workbook with similar issues. I could email it to you if easier?  What I did was create a "dates" table in excel with columns "range", "start" and "end" and then linked the table to powerpivot. If you change your end date in excel and then refresh the table in powerpivot all your dates are now in a powerpivot table. I created a "sales" table with just dates and sales amount.

I'm assuming you want to do something like sum the sales between these dates.

I created 3 new measures

Start Date: =min(Dates[Start])
End Date: =max(Dates[End])
total sales: =CALCULATE(sum(Sales[Sales]),filter(sales,Sales[Date]>Sales[Start Date] && Sales[Date]<Sales[End Date]+1))

You can use the Dates#VALUE!


----------



## MartinL (Jun 20, 2012)

Mike

I have just noticed that my text file that I am bringing in has a load of values missing in the powerpivot table have youever experienced that.
I have tried refreshing a number of times
Looking at the file in Notepad++ all the data is there but is just blank in the table

Martin


----------



## masplin (Jun 20, 2012)

Ok so the blanks in the table are because the data is mising in powerpivot?  I have only used powerpivot by linking excel tables. What format are your text files in? Could you make them into excel csv files and then try linking them?  If you want to send over the original file data I can have a look.


----------



## MartinL (Jun 22, 2012)

Mike

I got that one sorted, not sure what the issue was but it appeared to correct itself somehow.
But I have a new challenge with the same data, as I am getting to grips quite fast here.

I am trying to make a calculation within a pivot table that goes like this. Total Sales Last year + (Total Sales Last Year * Growth)
Where Growth = (Total Sales This Year / Total Sales Last Year) - 1

Martin


----------



## MartinL (Jun 22, 2012)

What I am trying to do Is replicate this:
*Sheet2*


*ABCDEFGHIJK1*Product GroupProductPrize2RetailerThis YearLast YearShareGrowthThis Year2Last Year2Share2Growth2PrizeUplift3Market463,165,805455,323,188-100.0%1.7%8,461,4747,866,828-1.8%7.6%£8,002,328£04Retailer 1150,451,568151,616,539-32.5%-0.8%1,606,3971,345,710-0.3%19.4%£1,335,370£05Retailer 2115,520,058111,812,669-24.9%3.3%2,561,0531,846,391-0.6%38.7%£1,907,612£06Retailer 347,516,39944,079,099-10.3%7.8% - *  - *  - *  - *  - * £07Retailer 447,094,88747,912,756-10.2%-1.7%266,59940-0.1%666397.5%£39£08Retailer 531,986,21433,273,955-6.9%-3.9%3,151,7033,806,167-0.7%-17.2%£3,658,864£507,1619Retailer 657,962,88455,398,421-12.5%4.6%657,147788,858-0.1%-16.7%£825,375£168,22810Retailer 7841,518854,786-0.2%-1.6%218,57679,6630.0%174.4%£78,426£011Retailer 811,792,27610,374,964-2.5%13.7% - *  - *  - *  - *  - * £0

 <colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 111px;"><col style="width: 78px;"><col style="width: 78px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 69px;"><col style="width: 68px;"><col style="width: 57px;"><col style="width: 78px;"><col style="width: 71px;"><col style="width: 74px;"></colgroup><tbody>

</tbody>

*Spreadsheet Formulas*
CellFormulaD8=-B8/$B$3E8=B8/C8-1H8=-F8/$B$3I8=F8/G8-1J8=G8+(G8*E8)K8=J8-F8

<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 

where the Prize uplift is <=0 then force 0 no negative values


----------



## masplin (Jun 22, 2012)

Ok so you want to see for each product whether they were over or under their overall sales growth. This where excel is more flexible as you dont have to have consistent formulae on a row.  Will you only have one product group with one product in the pivot driven by some filters? I think its going to involve using an ALL(Products) in the filters for the Group level  calc, but not sure how that will affect the rest of it. New one on me, but I like a challenge. Do you want to emial over your latest version?

Mike


----------



## masplin (Jun 26, 2012)

Bit stuck on this one. i know exactly what needs doing but don't have the syntax

I tried taking your total sales formula and trying to remove the "product group" filter which would give you the numbers for all products, but the cusomter filter would remain for each row. however just gives me blanks

=calculate(sum('Total Data Dump'[SalesValue]),FILTER('Total Data Dump',*ALL('Total Data Dump'[ProdGroup])*&&'Total Data Dump'[WkEnd]>='Total Data Dump'[Start Date]&&'Total Data Dump'[WkEnd]<'Total Data Dump'[End Date]+1))

I tried putting it in various places like before FILTER as a 1st filter, but gave me the same numbers you already had.  I have no idea why this doesn't just remove the Product group FILTER and do the calculation. If we could work this out we can then create a measure for  this year and last year and divide them to give the customer growth for all products, which is what I think you need to then calculate your other numbers.  Think we need someone cleverer to explain how to take out the column filter while leaving the date filter in place.

Mike


----------



## MartinL (Jun 26, 2012)

Thats ok Mike,

Thanks for your help so far, as you can see I have done a fair amount of work on it.
We will have to see if someone else will grab the gauntlet, with us.


----------



## masplin (Jun 26, 2012)

Feeling a bit chuffed as sort of got it working. I've uploaded a new file to the server, but essentially this is what I've done

1. Create a measure All Product Sales=CALCULATE(value('Total Data Dump'[Total Sales]),ALL('Total Data Dump'[ProdGroup])). so instead of trying to put the ALL inside the previous formula.  I think this is correct as if you select all Products numbers are the same.
2. New measure Customer Growth=CALCULATE('Total Data Dump'[All Product sales],Dates#VALUE!


----------



## MartinL (Jun 15, 2012)

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*


*ABCDE1GeographyBarcodeValue SalesStore Numeric DistributionDate2RetailerA8521808521262510/01/20093RetailerA8411397688358710/01/20094RetailerA1472175807746710/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*E4*StartEnd552WTY14 June 201105 June 2012652WLY15 June 201007 June 2011726WTY13 December 201105 June 2012826WLY14 December 201007 June 2011913WTY13 March 201205 June 20121013WLY15 March 201107 June 2011114WTY15 May 201205 June 2012124WLY17 May 201107 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*
CellFormulaD5=E5-(51*7)D6=E6-(51*7)E6=D5-7D7=E7-(25*7)E7=E5D8=E8-(25*7)E8=E6D9=E9-(12*7)E9=E5D10=E10-(12*7)E10=E6D11=E11-(3*7)E11=E5D12=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*


*S2549*2550LY

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

</tbody>

*Spreadsheet Formulas*
CellFormulaS2549=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 (Jun 26, 2012)

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 (Jun 26, 2012)

that was easy

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


----------



## masplin (Jun 26, 2012)

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 (Jun 26, 2012)

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

I will look into that one

Martin


----------

