Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a client who has asked for a Power BI version of this spreadsheet, basically:
I've tried to replicate this using a Matrix, but I can't get YoY underneath 2023 and 2024 - I've had to do them as measures and then add them to the values field so essentially it's the same data, it just reads left to right instead of top to bottom:
However, the client is insisting they want the sequence of rows to be Year, then Year on Year, then Year on Year Percentage. Is this actually possible? I can't see how I can do it without writing some monstrously complicated DAX that's going to eat up memory and even then I'm dubious.
Solved! Go to Solution.
Hi @BUC ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Create the measures as below
Current Year value = CALCULATE(SUM('Table'[Value]),YEAR('Table'[Date])=YEAR(TODAY()))
Previous year value = CALCULATE(SUM('Table'[Value]),YEAR('Table'[Date])=YEAR(TODAY())-1)
YoY = [Current Year value]-[Previous year value]
YoY% = DIVIDE([YoY],[Previous year value])
2. Create a matrix visual with the below Fields and format settings
Create a matrix visual
Toggle on the option "Switch values to rows"
Best Regards
Hi @BUC ,
It won't requrie a monstrously complicated DAX but a disconnected table as a placeholder and some DAX measures and a custom format string to return either a number or a percentage. Attached are sample pbixes to help you get started.
Hi there, thanks for your speedy response 🙂
I'm not sure this is what I'm after; I may be missing something though (still relatively new to Power BI).
Using your example, what I'd need is an additional row where I've put an arrow that shows Jan 2020 - Jan 2021. Is that possible? I already have the difference calculations (both Sum and Percentage), I just need to find out if I can have these values as rows underneath the first two years (I'm only comparing current year to previous year so my table would only ever have two rows per item).
Hi @BUC ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Create the measures as below
Current Year value = CALCULATE(SUM('Table'[Value]),YEAR('Table'[Date])=YEAR(TODAY()))
Previous year value = CALCULATE(SUM('Table'[Value]),YEAR('Table'[Date])=YEAR(TODAY())-1)
YoY = [Current Year value]-[Previous year value]
YoY% = DIVIDE([YoY],[Previous year value])
2. Create a matrix visual with the below Fields and format settings
Create a matrix visual
Toggle on the option "Switch values to rows"
Best Regards
That's exactly what I need - you're a genius! Thanks so much 🙂
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
73 | |
72 | |
38 | |
31 | |
26 |