Forum Discussion

Silvercrest's avatar
Silvercrest
Frequent Visitor
4 years ago
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...
Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    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]

     

     

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
4 years ago

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]