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

 

[masplin]

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]

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]

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]

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]

What I am trying to do Is replicate this:
Sheet2

*	A	B	C	D	E	F	G	H	I	J	K
*
Retailer	This Year	Last Year	Share	Growth	This Year2	Last Year2	Share2	Growth2	Prize	Uplift
Market
Retailer 1
Retailer 2
Retailer 3	- *	- *	- *	- *	- *
Retailer 4
Retailer 5
Retailer 6
Retailer 7
Retailer 8	- *	- *	- *	- *	- *

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

[TD="colspan: 4, align: center"]Product Group[/TD]
[TD="colspan: 4, align: center"]Product[/TD]
[TD="colspan: 2, align: center"]Prize[/TD]

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

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

[TD="align: right"]463,165,805[/TD]
[TD="align: right"]455,323,188[/TD]
[TD="align: right"]-100.0%[/TD]
[TD="align: right"]1.7%[/TD]
[TD="align: right"]8,461,474[/TD]
[TD="align: right"]7,866,828[/TD]
[TD="align: right"]-1.8%[/TD]
[TD="align: right"]7.6%[/TD]
[TD="align: right"]£8,002,328[/TD]
[TD="align: right"]£0[/TD]

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

[TD="align: right"]150,451,568[/TD]
[TD="align: right"]151,616,539[/TD]
[TD="align: right"]-32.5%[/TD]
[TD="align: right"]-0.8%[/TD]
[TD="align: right"]1,606,397[/TD]
[TD="align: right"]1,345,710[/TD]
[TD="align: right"]-0.3%[/TD]
[TD="align: right"]19.4%[/TD]
[TD="align: right"]£1,335,370[/TD]
[TD="align: right"]£0[/TD]

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

[TD="align: right"]115,520,058[/TD]
[TD="align: right"]111,812,669[/TD]
[TD="align: right"]-24.9%[/TD]
[TD="align: right"]3.3%[/TD]
[TD="align: right"]2,561,053[/TD]
[TD="align: right"]1,846,391[/TD]
[TD="align: right"]-0.6%[/TD]
[TD="align: right"]38.7%[/TD]
[TD="align: right"]£1,907,612[/TD]
[TD="align: right"]£0[/TD]

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

[TD="align: right"]47,516,399[/TD]
[TD="align: right"]44,079,099[/TD]
[TD="align: right"]-10.3%[/TD]
[TD="align: right"]7.8%[/TD]

[TD="align: right"]£0[/TD]

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

[TD="align: right"]47,094,887[/TD]
[TD="align: right"]47,912,756[/TD]
[TD="align: right"]-10.2%[/TD]
[TD="align: right"]-1.7%[/TD]
[TD="align: right"]266,599[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]-0.1%[/TD]
[TD="align: right"]666397.5%[/TD]
[TD="align: right"]£39[/TD]
[TD="align: right"]£0[/TD]

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

[TD="align: right"]31,986,214[/TD]
[TD="align: right"]33,273,955[/TD]
[TD="align: right"]-6.9%[/TD]
[TD="align: right"]-3.9%[/TD]
[TD="align: right"]3,151,703[/TD]
[TD="align: right"]3,806,167[/TD]
[TD="align: right"]-0.7%[/TD]
[TD="align: right"]-17.2%[/TD]
[TD="align: right"]£3,658,864[/TD]
[TD="align: right"]£507,161[/TD]

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

[TD="align: right"]57,962,884[/TD]
[TD="align: right"]55,398,421[/TD]
[TD="align: right"]-12.5%[/TD]
[TD="align: right"]4.6%[/TD]
[TD="align: right"]657,147[/TD]
[TD="align: right"]788,858[/TD]
[TD="align: right"]-0.1%[/TD]
[TD="align: right"]-16.7%[/TD]
[TD="align: right"]£825,375[/TD]
[TD="align: right"]£168,228[/TD]

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

[TD="align: right"]841,518[/TD]
[TD="align: right"]854,786[/TD]
[TD="align: right"]-0.2%[/TD]
[TD="align: right"]-1.6%[/TD]
[TD="align: right"]218,576[/TD]
[TD="align: right"]79,663[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]174.4%[/TD]
[TD="align: right"]£78,426[/TD]
[TD="align: right"]£0[/TD]

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

[TD="align: right"]11,792,276[/TD]
[TD="align: right"]10,374,964[/TD]
[TD="align: right"]-2.5%[/TD]
[TD="align: right"]13.7%[/TD]

[TD="align: right"]£0[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D8 =-B8/$B$3 E8 =B8/C8-1 H8 =-F8/$B$3 I8 =F8/G8-1 J8 =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]

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]

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]

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]

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!