Forum Discussion
Solved
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 o...
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
Super User
Super UserGlad 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 )
-----