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

[Canadianoutside787]

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
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
3	Retail / E-Com	DTC Shop ID	DTC Format	Prod. hier. level 1	Prod. hier. level 2	Product Type	Gender	Material #	Article Name	Calendar Year/Week	Week	Year	Inv Gross Sales	Inv Net Sales	Inv Net Units	Inv Gross Units	Returns value	Returns units	Stock in Store	Stock in Transit
4	RETAIL	Shop 1	BS	C1 helmets	Alp. Helmets.ON	Helmet	F	38400	HELMET 1	03.2020	03	2020	115.7	115.7	1	1
5	RETAIL	Shop 2	BS	C1 Jackets	Alp. Jackets.ON	Helmet	M	37300	Jacket 2	51.2019	51	2019	140.5	140.5	1	1
6	RETAIL	Shop 1	BS	C1 Jackets	APP Active Wear	Midlayer Jacket	F	55800	midlayer 1		34	2020							7	0
7	RETAIL	Shop 1	BS	C1 Jackets	APP Essentials	Down Jacket	F	89900	Down jacket 1		34	2020							7	0
8	RETAIL	Shop 2	BS	C1 Jackets	APP Essentials	Down Jacket	F	90000	Down jacket 2		34	2020							5	0
9	RETAIL	Shop 1	PS	C1 Socks	Running socks	Socks	U	4700	Socks 1	33.2020	33	2020	72.72	72.72	4	4
10	RETAIL	Shop 1	PS	C1 Socks	Running socks	Socks	U	4700	Socks 1	34.2020	34	2020	36.36	36.36	2	2
11	RETAIL	Shop 2	PS	C1 Socks	Running socks	Socks	U	4700	Socks 1	32.2020	32	2020	12.34	12.34	1	1
Sheet1

Cell Formulas
Range	Formula
L4:L5,L9:L11	L4	=LEFT([@[Calendar Year/Week]],2)
M4:M5,M9:M11	M4	=RIGHT([@[Calendar Year/Week]],4)

 

[Canadianoutside787]

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]

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!