Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |