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
ShaunGibbs23
Regular Visitor

Yearly Change Percentage on Matrix

Hi, I'm trying to add '% Change from Last Year' column onto this matrix. Is it possible to add in a column that looks at one year (e.g. 2022) then works out the percentage difference to the year prior (e.g. 2021)? Photo attached. Do I need to create a measure? Power BI - yearly change.jpg

 



1 ACCEPTED SOLUTION

8 REPLIES 8
Padycosmos
Solution Sage
Solution Sage

Hope this helps

Padycosmos_0-1674729965861.png

 

@Padycosmos It actually has stopped showing the error but when you add it to the table, it says they are all 0.99. I think that may be because the matrix shown has a filter on it for 'Provider Name' as it shows just the data from one university whereas the dataset/domicile table has all. Is there a way around this without creating a new dataset that only includes the provider I am showing the matrix?

 

Power BI - percent change -0.00.jpg

Padycosmos_0-1674740697698.png

 

Hi @Padycosmos, my only issue with this is that it does not seem to work as the CY and PY after var do not go green like yours, or just the last letter does? I have tried putting in extar spaces but does not seem to work.

 

Thanks for your help1

Could you share the screenshot of the measure?

Greg_Deckler
Super User
Super User

@ShaunGibbs23 So like this?

Measure =
  VAR __ThisYear = SUM('Table'[Applications])
  VAR __LastYear = SUMX(FILTER('Table',[Year] = MAX('Table'[Year] -1),[Applications])
  VAR __Diff = __ThisYear - __LastYear
  VAR __Result = DIVIDE(__Diff, __LastYear,0)
RETURN
  __Result

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks for your help. When I try the following syntax, when I get to VAR_Diff, it only recognises VAR_This Year and not Last Year. Do you have any idea what may be going wrong with the second line? Attached is a photo of the error bar (my table/data is called Domicile in the error that I have changed below for ease).

 

Yearly Change =
    VAR __ThisYear = SUM(Table[Applications.])
    VAR __LastYear = SUMX(FILTER('Table',[Year] = MAX('Table'[Year] -1, [Applications.]
Power BI - Syntax Error.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.