Forum Discussion
Simple Matrix with Variance
HI there
I am a very new user to Power Bi, and struggling with something I thought must be a simple visualization.
For my data I need to show a matrix, years in the columns and months in the rows, with the spend per month/year shown. This I can do easily.
My data is very basic.
However, I also need to show the variance from last year to this year, and this is where I get stuck! Using co-pilot it supplied a dax formula, however that also shows the variance for last year (against nothing so shows as a minus) and it seems this can't be hidden/excluded.
The end result i need is:
2024 2025 Variance
Jan 24.50 39.21 14.71
Feb 13.95 24.56 10.61
Mar 32.10 29.55 -2.55
etc
Is this doable? I am trying to show my company, and those that make the decisions, the Power Bi is a worthwhile expense, but struggling with this very basic starting point.
The data can be run in any way needed. I'm quite proficient with Power Query so that side isn't an issue.
many thanks
Hello BarryWhitelaw
Try these measure
1.Total Spend = SUM('Table'[Spend])
2.Previous Year Spend =
CALCULATE(
[Total Spend],
SAMEPERIODLASTYEAR('Table'[Date])
)3.Variance =
[Total Spend] - [Previous Year Spend]
4.Variance =
VAR Curr = [Total Spend]
VAR Prev = [Previous Year Spend]
RETURN
IF(
NOT ISBLANK(Curr) && NOT ISBLANK(Prev),
Curr - Prev
)
If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.
Thanks,
Connect with me on:
LinkedInPlease try the approaches in the attached pbix.
Selected years:
Max and previous years:
3 Replies
Hello BarryWhitelaw
Try these measure
1.Total Spend = SUM('Table'[Spend])
2.Previous Year Spend =
CALCULATE(
[Total Spend],
SAMEPERIODLASTYEAR('Table'[Date])
)3.Variance =
[Total Spend] - [Previous Year Spend]
4.Variance =
VAR Curr = [Total Spend]
VAR Prev = [Previous Year Spend]
RETURN
IF(
NOT ISBLANK(Curr) && NOT ISBLANK(Prev),
Curr - Prev
)
If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.
Thanks,
Connect with me on:
LinkedInPlease try the approaches in the attached pbix.
Selected years:
Max and previous years:
Thank you both for your replies. This is a prime example of trying to run before I can walk!
After watching some YouTube videos it dawned on me that I should be creating a measure for each period, which would then allow me to use those measures in a calculation! Once I realsied that, it was of course a simple task!
Note to brain, training!
