How to author Power BI Tablix

 Check list of all posts

The report below looks very simple but can't be easily built , as we need to show 3 years DYNAMICALLY. We can make such a report with Tablix functionality of SSRS, or discontinued crosstab from Cognos. 

Sample source data

Option 1: Table, but headers have to be hard-coded

CurrentYear = YEAR ( TODAY () )

CYSales  (rename to 2020)= CALCULATE (

    SUM ( TableX[Sales] ),
    FILTER ( TableX, TableX[Year] = [CurrentYear] ) 

) 

PYSales  (rename to 2019)= CALCULATE (

    SUM ( TableX[Sales] ),
    FILTER ( TableX, TableX[Year] = [CurrentYear] - 1) 

) 

PYPYSales  (rename to 2018)= CALCULATE (

    SUM ( TableX[Sales] ),
    FILTER ( TableX, TableX[Year] = [CurrentYear] - 2 ) 

) 

PYPYSales  (rename to 2018)= CALCULATE (

    SUM ( TableX[Sales] ),
    FILTER ( TableX, TableX[Year] = [CurrentYear] - 2 ) 

) 

CY-PY(rename to YoY)= [CYSales] - [PYSales]

CY-PY KPI(rename to YoY)= SWITCH (

    TRUE (),
    [CY-PY] < 0, UNICHAR ( 9660 ),
    [CY-PY] > 0, UNICHAR ( 9650 ),
    BLANK ()
)

Option 2: Matrix with the duplicated header, Sales and YoY

YoY=

VAR prev_year =
    SELECTEDVALUE ( TableX[Year], 0 ) - 1
RETURN
    SUM ( TableX[Sales] )
        - CALCULATE (
            SUM ( TableX[Sales] ),
            ALL ( TableX[IsLast3Yrs] ),
            TableX[Year] = prev_year
        )

YoY KPI=

SWITCH (
    TRUE (),
    [YoY] < 0, UNICHAR ( 9660 ),
    [YoY] > 0, UNICHAR ( 9650 ),
    BLANK ()
)

Option 3: Conditional formatting with icons in Power BI