Simple Matrix with Variance

[BarryWhitelaw]

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

[pankajnamekar25 · Accepted solution]

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:

LinkedIn

[BarryWhitelaw]

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!

[danextian · Accepted solution]

Hi @BarryWhitelaw 

Please try the approaches in the attached pbix.

Selected years:

Max and previous years:

[pankajnamekar25 · Accepted solution]

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:

LinkedIn