PowerPivot Year on Year values

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,110
Office Version
  1. 365
Platform
  1. Windows
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

*ABCDE
1GeographyBarcodeValue SalesStore Numeric DistributionDate
2RetailerA8521808521262510/01/2009
3RetailerA8411397688358710/01/2009
4RetailerA1472175807746710/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
*CDE
4*StartEnd
552WTY14 June 201105 June 2012
652WLY15 June 201007 June 2011
726WTY13 December 201105 June 2012
826WLY14 December 201007 June 2011
913WTY13 March 201205 June 2012
1013WLY15 March 201107 June 2011
114WTY15 May 201205 June 2012
124WLY17 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
CellFormula
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*
2550LY

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

Spreadsheet Formulas
CellFormula
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
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

masplin

Active Member
Joined
May 10, 2010
Messages
411
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

Well-known Member
Joined
Oct 16, 2008
Messages
1,110
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
May 10, 2010
Messages
411
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

Well-known Member
Joined
Oct 16, 2008
Messages
1,110
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 16, 2008
Messages
1,110
Office Version
  1. 365
Platform
  1. Windows
What I am trying to do Is replicate this:
Sheet2

*ABCDEFGHIJK
1*Product GroupProductPrize
2RetailerThis YearLast YearShareGrowthThis Year2Last Year2Share2Growth2PrizeUplift
3Market463,165,805455,323,188-100.0%1.7%8,461,4747,866,828-1.8%7.6%£8,002,328£0
4Retailer 1150,451,568151,616,539-32.5%-0.8%1,606,3971,345,710-0.3%19.4%£1,335,370£0
5Retailer 2115,520,058111,812,669-24.9%3.3%2,561,0531,846,391-0.6%38.7%£1,907,612£0
6Retailer 347,516,39944,079,099-10.3%7.8% - * - * - * - * - * £0
7Retailer 447,094,88747,912,756-10.2%-1.7%266,59940-0.1%666397.5%£39£0
8Retailer 531,986,21433,273,955-6.9%-3.9%3,151,7033,806,167-0.7%-17.2%£3,658,864£507,161
9Retailer 657,962,88455,398,421-12.5%4.6%657,147788,858-0.1%-16.7%£825,375£168,228
10Retailer 7841,518854,786-0.2%-1.6%218,57679,6630.0%174.4%£78,426£0
11Retailer 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
CellFormula
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
 
Last edited:

masplin

Active Member
Joined
May 10, 2010
Messages
411

ADVERTISEMENT

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

Active Member
Joined
May 10, 2010
Messages
411
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

Well-known Member
Joined
Oct 16, 2008
Messages
1,110
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
May 10, 2010
Messages
411
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top