Power Pivot/Power Query - Adding Year over Year and other calculations to Pivot Tables

Canadianoutside787

New Member
Joined
Aug 26, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello Excel Community,

I'm hoping you can help me with building some calculations into my data model in Power Pivot. For some context: I work for a retail company and I am looking to build the dashboard that lets me analyze how our stores and their products have been performing. The data set is too large to build pivot tables straight off of which is why I originally wanted to use power query but I am slowly learning that there are a lot of other advantages. However, I feel like I'm in over my head right now and the world is spinning. For a first step, I would just like to bring in YoY performances for our stores.

Ideally, I will build 4 different pivot tables that show me the following with their year over year growth/decline: Last week performances , Last month, season to date, and year to date. For an example, for a last week calculation I would want to see the growth between the "calendar year/week" column for 34.2020 and 34.2019...we go by fiscal weeks at our company.

I have included an example data set (for confidentiality reasons) which is very similar to what I'm working with but with only a couple lines for last year sales, this year sales and current inventory in the stores.

All that being said....could someone help me with a formula that calculates the year over year performance of this data? I've also attached an image of what the power pivot table looks like right now at a basic level.

thanks a bunch to all of you for your words of wisdom!

Example data table for data model.xlsx
BCDEFGHIJKLMNOPQRSTU
3Retail / E-ComDTC Shop IDDTC FormatProd. hier. level 1Prod. hier. level 2Product TypeGenderMaterial #Article NameCalendar Year/WeekWeekYearInv Gross SalesInv Net SalesInv Net Units Inv Gross UnitsReturns valueReturns unitsStock in StoreStock in Transit
4RETAILShop 1BSC1 helmetsAlp. Helmets.ONHelmetF38400HELMET 103.2020032020115.7115.711
5RETAILShop 2BSC1 JacketsAlp. Jackets.ONHelmetM37300Jacket 251.2019512019140.5140.511
6RETAILShop 1BSC1 JacketsAPP Active WearMidlayer JacketF55800midlayer 134202070
7RETAILShop 1BSC1 JacketsAPP EssentialsDown JacketF89900Down jacket 134202070
8RETAILShop 2BSC1 JacketsAPP EssentialsDown JacketF90000Down jacket 234202050
9RETAILShop 1PSC1 SocksRunning socksSocksU4700Socks 133.202033202072.7272.7244
10RETAILShop 1PSC1 SocksRunning socksSocksU4700Socks 134.202034202036.3636.3622
11RETAILShop 2PSC1 SocksRunning socksSocksU4700Socks 132.202032202012.3412.3411
Sheet1
Cell Formulas
RangeFormula
L4:L5,L9:L11L4=LEFT([@[Calendar Year/Week]],2)
M4:M5,M9:M11M4=RIGHT([@[Calendar Year/Week]],4)
 

Attachments

  • last week pivot table example.JPG
    last week pivot table example.JPG
    43.6 KB · Views: 2

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Canadianoutside787

New Member
Joined
Aug 26, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Sorry, I forgot to mention that I tried to use the calculate DAX function with the time intelligent function of "sameperiodlastyear" using the column "calendar year/week" that you see in the data set but it didn't work. Our entire company uses this fiscal week system of time to look at our sales but I don't think excel recognizes it as a format of time which could be the base of the problem.
 

Canadianoutside787

New Member
Joined
Aug 26, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi again,

I wanted to share how far I've gotten. I was able to build a YoY calculated field but it is definitely not correct.

One of the changes I made was change that date format from week.year to an actual date for excel to have an easier time understanding it. Next, I did the a calculate function, that I've attached a screen shot of, that did not work here.

Any notes of help is appreciated! Thanks!
 

Attachments

  • Pivot table with incorrect YoY figure.JPG
    Pivot table with incorrect YoY figure.JPG
    43.9 KB · Views: 4
  • incorrect calculate function for sameperiodlastyear.JPG
    incorrect calculate function for sameperiodlastyear.JPG
    34.5 KB · Views: 4

Watch MrExcel Video

Forum statistics

Threads
1,113,779
Messages
5,544,179
Members
410,596
Latest member
JoeyZ
Top