Replicating an excel table with many measures

[Silvercrest]

Hi,

I am trying to replicate a managers much loved spreadsheet in PowerBI. In excel, he has a table like this, but with 65 rows. So we are trying to achieve this:

	This Year	Last Year	Year on Year difference
Staff	65	60	5
Revenue	100	110	-10
Profit	20	23	-3

The data comes into PowerBI in a csv in the form 

Measure	Year	Value
Staff	2022	65
Staff	2021	60
Revenue	2022	100
Revenue	2021	110
Profit	etc

which gives me a table called PIs.

How do I do this ? I can do it using UNION and ROW but only if I effectively hardcode the year for last year to 2021. Of course I want it to still work in future years. I can't seem to make an external filter take effect. I know all about the Show values on rows option, but that does not let me calculate the Year on Year Difference.

This is my first request for help , so all suggestions very gratefully received!

Thank you

[Silvercrest]

Thank you, that achieved what I needed. 😀 thank you also for the prompt reply - I had spent hours on that.

As an encore, can you suggest how the same thing could be achieved but with the lastest year being configurable by the user through a filter - ie if they wanted to go and see what the same figures were last year, so 2021

compared to 2020?

[DataInsights]

Glad to hear that worked. Here's the encore. 🙂

Measures:

This Year = 
CALCULATE ( MAX ( PIs[Value] ), PIs[Year] = SELECTEDVALUE ( PIs[Year] ) )

Last Year = 
CALCULATE ( MAX ( PIs[Value] ), PIs[Year] = SELECTEDVALUE ( PIs[Year] ) - 1 )

-----

[DataInsights · Accepted solution]

@Silvercrest,

Try these measures:

This Year = 
VAR vMaxYear =
    CALCULATE ( MAX ( PIs[Year] ), ALL ( PIs ) )
VAR vResult =
    CALCULATE ( MAX ( PIs[Value] ), PIs[Year] = vMaxYear )
RETURN
    vResult

Last Year = 
VAR vMaxYear =
    CALCULATE ( MAX ( PIs[Year] ), ALL ( PIs ) )
VAR vResult =
    CALCULATE ( MAX ( PIs[Value] ), PIs[Year] = vMaxYear - 1 )
RETURN
    vResult

Year on Year Difference = [This Year] - [Last Year]