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

 

[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
1	*	Product Group	Product	Prize
2	Retailer	This Year	Last Year	Share	Growth	This Year2	Last Year2	Share2	Growth2	Prize	Uplift
3	Market	463,165,805	455,323,188	-100.0%	1.7%	8,461,474	7,866,828	-1.8%	7.6%	£8,002,328	£0
4	Retailer 1	150,451,568	151,616,539	-32.5%	-0.8%	1,606,397	1,345,710	-0.3%	19.4%	£1,335,370	£0
5	Retailer 2	115,520,058	111,812,669	-24.9%	3.3%	2,561,053	1,846,391	-0.6%	38.7%	£1,907,612	£0
6	Retailer 3	47,516,399	44,079,099	-10.3%	7.8%	- *	- *	- *	- *	- *	£0
7	Retailer 4	47,094,887	47,912,756	-10.2%	-1.7%	266,599	40	-0.1%	666397.5%	£39	£0
8	Retailer 5	31,986,214	33,273,955	-6.9%	-3.9%	3,151,703	3,806,167	-0.7%	-17.2%	£3,658,864	£507,161
9	Retailer 6	57,962,884	55,398,421	-12.5%	4.6%	657,147	788,858	-0.1%	-16.7%	£825,375	£168,228
10	Retailer 7	841,518	854,786	-0.2%	-1.6%	218,576	79,663	0.0%	174.4%	£78,426	£0
11	Retailer 8	11,792,276	10,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

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!