Calculating Year over Year change % with multiple stores showing sales in same month???

[bcsalsaboy]

Hello everyone.  I too am a brand new user of Power BI,  less then 30 days under my belt.  Although I have been able to figure a number of things after watching the videos and seeing online help forums, I am having 1 major issue though.

I am trying to calculate YOY % change for my data   not only at my store location level, but for the total of all locations as well.   I only have year and month in my data source,   no dates (yet).  But would appreciate someone showing me how i can create the calculations or measures to get my % (using the colum header names I supplied).   Sample of my data is below.   Thank you in advance.

YEAR	MONTH SHORT	CHANNEL LOCATION NAME	MONTH2	Mo YR	SALES
2018	JAN	STORE 124	01	JAN 2018	55
2018	FEB	STORE 124	02	FEB 2018	83
2018	MAR	STORE 124	03	MAR 2018	88
2018	APR	STORE 124	04	APR 2018	52
2018	MAY	STORE 124	05	MAY 2018	90
2018	JUN	STORE 124	06	JUN 2018	100
2018	JUL	STORE 124	07	JUL 2018	73
2018	AUG	STORE 124	08	AUG 2018	113
2018	SEP	STORE 124	09	SEP 2018	85
2018	OCT	STORE 124	10	OCT 2018	95
2018	NOV	STORE 124	11	NOV 2018	102
2018	DEC	STORE 124	12	DEC 2018	133
2019	JAN	STORE 124	01	JAN 2019	54
2019	FEB	STORE 124	02	FEB 2019	55
2019	MAR	STORE 124	03	MAR 2019	38
2018	JAN	STORE 250	01	JAN 2018	3
2018	FEB	STORE 250	02	FEB 2018	8
2018	MAR	STORE 250	03	MAR 2018	9
2018	APR	STORE 250	04	APR 2018	5
2018	MAY	STORE 250	05	MAY 2018	6
2018	JUN	STORE 250	06	JUN 2018	18
2018	JUL	STORE 250	07	JUL 2018	10
2018	AUG	STORE 250	08	AUG 2018	7
2018	SEP	STORE 250	09	SEP 2018	9
2018	OCT	STORE 250	10	OCT 2018	8
2018	NOV	STORE 250	11	NOV 2018	12
2018	DEC	STORE 250	12	DEC 2018	8
2019	JAN	STORE 250	01	JAN 2019	4
2019	FEB	STORE 250	02	FEB 2019	13
2019	MAR	STORE 250	03	MAR 2019	2
2018	JAN	STORE 4000	01	JAN 2018	24
2018	FEB	STORE 4000	02	FEB 2018	43
2018	MAR	STORE 4000	03	MAR 2018	46
2018	APR	STORE 4000	04	APR 2018	30
2018	MAY	STORE 4000	05	MAY 2018	44
2018	JUN	STORE 4000	06	JUN 2018	35
2018	JUL	STORE 4000	07	JUL 2018	51
2018	AUG	STORE 4000	08	AUG 2018	50
2018	SEP	STORE 4000	09	SEP 2018	40
2018	OCT	STORE 4000	10	OCT 2018	47
2018	NOV	STORE 4000	11	NOV 2018	44
2018	DEC	STORE 4000	12	DEC 2018	34
2019	JAN	STORE 4000	01	JAN 2019	28
2019	FEB	STORE 4000	02	FEB 2019	36
2019	MAR	STORE 4000	03	MAR 2019	19
2018	JAN	STORE 999	01	JAN 2018	90
2018	FEB	STORE 999	02	FEB 2018	89
2018	MAR	STORE 999	03	MAR 2018	212
2018	APR	STORE 999	04	APR 2018	97
2018	MAY	STORE 999	05	MAY 2018	112
2018	JUN	STORE 999	06	JUN 2018	180
2018	JUL	STORE 999	07	JUL 2018	115
2018	AUG	STORE 999	08	AUG 2018	123
2018	SEP	STORE 999	09	SEP 2018	100
2018	OCT	STORE 999	10	OCT 2018	101
2018	NOV	STORE 999	11	NOV 2018	168
2018	DEC	STORE 999	12	DEC 2018	249
2019	JAN	STORE 999	01	JAN 2019	112
2019	FEB	STORE 999	02	FEB 2019	85
2019	MAR	STORE 999	03	MAR 2019	101

[v-frfei-msft · Accepted solution]

Hi @bcsalsaboy ,

One sample for your reference. Please check the following steps.

1. Create a calcualted column in fact table.

Date = DATE(Table1[YEAR],Table1[MONTH2],1)

2. Create a  CALENDAR table and create relationship between fact table and it by the date columns.

CALENDAR = CALENDAR(DATE(2018,01,01),DATE(2019,12,31))

3. Create a measure to get the YOY.

Measure = DIVIDE(SUM(Table1[SALES]),CALCULATE(SUM(Table1[SALES]),SAMEPERIODLASTYEAR('CALENDAR'[Date].[Date])))

Regards,

Frank

[chass]

Great Reply Thom!

[TomMartens]

Hey @bcsalsaboy ,

even if your data may not contain data that is on a day level you should consider to make it daily, e.g. just adding the 1st or last day of a month, then create a dedicated calendar table.

From my experience, this site https://www.daxpatterns.com/time-patterns/ contains almost everything about time intelligence calculations.

Regards,

Tom