Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yvonnedlr
Regular Visitor

Trying to calculate percentage difference

 

I have a matrix which has a number of groups down the side and also across the top and under each of the top groups there is a total of sales for each year, in this instance it is 2018 and 2019. I am trying to add a 3rd column alongside 2018 and 2019 which calculates the percentage difference between 2018 and 2019.  I just cannot figure out how to do this.

I am really new to Power BI so any assistance would be appreciated

3 REPLIES 3
Anonymous
Not applicable

If M is the measure, and the model has a proper Date table, then:

 

 

[M YoY %] =
var __current = [M]
var __today = today()
-- __effectiveDates limits the
-- current period to only (the
-- past + today) since comparing
-- the whole of 2019 to the whole of
-- 2018 means you are comparing the past
-- and some of the future to the past
-- which is obviously misleading. If
-- today is 1 June, say, then you want
-- to compare 2019 against 2018 but only
-- up to 1 June. Right? if this is not
-- what you want, then remove __effectiveDates
-- and in __prevYear replace the condition
-- by sameperiodlastyear( Dates[Date] )
var __effectiveDates =
	calculatetable (
		values ( Dates[Date] ),
		Dates[Date] <= __today
	)
var __prevYear =
	calculate(
		[M],
		-- Dates is the proper Date table that
		-- is connected to your sales fact table.
		sameperiodlastyear ( __effectiveDates )
	)
var __yoy =
	divide(
		__current - __prevYear,
		__prevYear
	)
return
	__yoy

Best

Darek

Thanks, I will give that a try and let you know how I go

 

Yvonne

I have had a go at this but it is not working for me. Clearly I am doing something wrong. Not experienced enough to pick up what is wrong. This picture reflects what I am trying to achieve, the difference between 2018 and 2019. I keep on getting a column after each column. Just one need after each group

 

pbi.jpg

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors