Calculate YoY change as a column next to year

[Touliloop]

I have a dataset as following, for each country and each year and each metric there is only one row.

Country	Year	Cost	Customers	Orders
CN	2025	100	10	150
US	2025	200	80	60
UK	2025	150	15	40
CN	2024	200	20	15
UK	2024	300	30	100
US	2024	300	40	90
FR	2024	150	10	100

I would like to have a matrix, in which Year is the column, beside the Year column is a column "YoY" which calculate the YoY growth of each metric. As row it should ideally shows the name of the metric. Country would be filtered by a slicer. An wished output would be as below, (if a country doesn't have current year or previous year then YoY show as empty) assume we select the country US using the slicer:

	2024	2025	YoY
Cost	300	200	-33%
Customers	40	80	100%
Orders	90	60	-50%

[hnguy71 · Accepted solution]

Hi @Touliloop ,

Alright. I'm going to assume two things:

1. You're not planning on using a Date table.

2. You're going to allow users to elect which year he/she wants to evaluate to analyze the two years.

Step 01: Create a table that you can link to your fact table with a custom row for "YoY"'

Years = 

VAR _Years = VALUES('Fact'[Year])
VAR _TotalRow = ROW("Year", "YoY")
RETURN
UNION(_Years, _TotalRow)

Step 02: Connect Year to Year

Step 03: Create a disconnected table to allow your users to select which year they want to evaluate

SelectYears = 

VAR _Years = VALUES('Fact'[Year])
RETURN
_Years

Step 04: Create the year over year measure. Of course, substitute your_base_measure with your actual measure.

YearOverYear = 

VAR _SelectedYear = SELECTEDVALUE(SelectYears[Year], CALCULATE(MAX(SelectYears[Year]), ALL(SelectYears)))
VAR _PreviousYear = _SelectedYear - 1

VAR _YearInContext = SELECTEDVALUE(Years[Year])

RETURN

SWITCH( TRUE(),
    _YearInContext = "YOY", 
        VAR _Current = CALCULATE([YOUR_BASE_MEASURE], ALL(Years), KEEPFILTERS('Fact'[Year] = _SelectedYear))
        VAR _Previous = CALCULATE([YOUR_BASE_MEASURE], ALL(Years), KEEPFILTERS('Fact'[Year] = _PreviousYear))
        RETURN
        DIVIDE(_Current, _Previous),
    [YOUR_BASE_MEASURE]
)

Step 05: Adjust your dynamic formatting on the measure

VAR _Year = MAX(Years[Year])
RETURN

SWITCH( TRUE(),
    _Year = "YOY", "0%;-0%;0%",
    "0.00"
)

All that's left is to plug all the columns and measures into a matrix like so

Now, my shown output is different from yours because your sample data doesn't have an attritbute called "Cost" or "Customer", or "Order".  You would have to unpivot your data in order to get that granularity, or alternatively, build a dimesion for these attributes (similar to how we've just created the Year). Your matrix rows and columns cannot be measures.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.  Solved using calcualtion groups

[hnguy71]

Hi @Touliloop 

Two questions:

1. Will you always be evaluating these three fields?

2. Do you always want to show the year numbers?

[hnguy71 · Accepted solution]

Hi @Touliloop ,

Alright. I'm going to assume two things:

1. You're not planning on using a Date table.

2. You're going to allow users to elect which year he/she wants to evaluate to analyze the two years.

Step 01: Create a table that you can link to your fact table with a custom row for "YoY"'

Years = 

VAR _Years = VALUES('Fact'[Year])
VAR _TotalRow = ROW("Year", "YoY")
RETURN
UNION(_Years, _TotalRow)

Step 02: Connect Year to Year

Step 03: Create a disconnected table to allow your users to select which year they want to evaluate

SelectYears = 

VAR _Years = VALUES('Fact'[Year])
RETURN
_Years

Step 04: Create the year over year measure. Of course, substitute your_base_measure with your actual measure.

YearOverYear = 

VAR _SelectedYear = SELECTEDVALUE(SelectYears[Year], CALCULATE(MAX(SelectYears[Year]), ALL(SelectYears)))
VAR _PreviousYear = _SelectedYear - 1

VAR _YearInContext = SELECTEDVALUE(Years[Year])

RETURN

SWITCH( TRUE(),
    _YearInContext = "YOY", 
        VAR _Current = CALCULATE([YOUR_BASE_MEASURE], ALL(Years), KEEPFILTERS('Fact'[Year] = _SelectedYear))
        VAR _Previous = CALCULATE([YOUR_BASE_MEASURE], ALL(Years), KEEPFILTERS('Fact'[Year] = _PreviousYear))
        RETURN
        DIVIDE(_Current, _Previous),
    [YOUR_BASE_MEASURE]
)

Step 05: Adjust your dynamic formatting on the measure

VAR _Year = MAX(Years[Year])
RETURN

SWITCH( TRUE(),
    _Year = "YOY", "0%;-0%;0%",
    "0.00"
)

All that's left is to plug all the columns and measures into a matrix like so

Now, my shown output is different from yours because your sample data doesn't have an attritbute called "Cost" or "Customer", or "Order".  You would have to unpivot your data in order to get that granularity, or alternatively, build a dimesion for these attributes (similar to how we've just created the Year). Your matrix rows and columns cannot be measures.

[Touliloop]

Hi @hnguy71 

I have a question, what if I want for when Country = US, I would like the YoY to be 150 - 300 instead of (150 - 300) / 300? I am facing this problem now, tried lotsof things it didn't work...Do you happen to know how to do that?

[hnguy71]

Hi @Touliloop 

Okay, 1 more question before I assist. Do you use a date table by any chance?

[Touliloop]

I do have a date table, however, I didn't connect it with this table since I only have Year in this table.

[Touliloop]

Hi @hnguy71 

1) yeah I would like to always evalute these three fields. In fact, in reality I have more columns to be evaluated, this is just an example.

2) Yeah I want to have the year showed as columns.