Forum Discussion
Replicating an excel table with many measures
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
Try these measures:
This Year = VAR vMaxYear = CALCULATE ( MAX ( PIs[Year] ), ALL ( PIs ) ) VAR vResult = CALCULATE ( MAX ( PIs[Value] ), PIs[Year] = vMaxYear ) RETURN vResultLast Year = VAR vMaxYear = CALCULATE ( MAX ( PIs[Year] ), ALL ( PIs ) ) VAR vResult = CALCULATE ( MAX ( PIs[Value] ), PIs[Year] = vMaxYear - 1 ) RETURN vResultYear on Year Difference = [This Year] - [Last Year]
3 Replies
- DataInsights
Super User
Try these measures:
This Year = VAR vMaxYear = CALCULATE ( MAX ( PIs[Year] ), ALL ( PIs ) ) VAR vResult = CALCULATE ( MAX ( PIs[Value] ), PIs[Year] = vMaxYear ) RETURN vResultLast Year = VAR vMaxYear = CALCULATE ( MAX ( PIs[Year] ), ALL ( PIs ) ) VAR vResult = CALCULATE ( MAX ( PIs[Value] ), PIs[Year] = vMaxYear - 1 ) RETURN vResultYear on Year Difference = [This Year] - [Last Year] 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 )-----
