YoY Growth, data from previous year

[Joakimnn]

Hello Experts, 

I'm in dire need of your assistance with the following issue.

I want to retrieve data from a table to fetch values from previous years and determine the growth YoY.

As of now, the table looks like this:

Year	Region	Make	Model	Sales
2020	North America	Ford	Fiesta	1000
2020	Japan	Nissan	Altima	1500
2020	Europe	Hyundai	Elantra	800
2021	North America	Ford	Fiesta	1100
2021	China	Nissan	Altima	1600
2021	Europe	Hyundai	Elantra	900
2022	North America	Ford	Fiesta	1200
2022	China	Nissan	Altima	1700
2022	Europe	Hyundai	Elantra	1000
2023	North America	Ford	Fiesta	1300
2023	China	Nissan	Altima	1800
2023	Europe	Hyundai	Elantra	1100
2024	North America	Ford	Fiesta	1400
2024	China	Nissan	Altima	1900
2024	Europe	Hyundai	Elantra	1200

I need the result to be something along the lines of the following table:

Year	Region	Make	Model	Sales	Last Year Sales
2020	North America	Ford	Fiesta	1000
2020	Japan	Nissan	Altima	1500
2020	Europe	Hyundai	Elantra	800
2021	North America	Ford	Fiesta	1100	1000
2021	China	Nissan	Altima	1600	1500
2021	Europe	Hyundai	Elantra	900	800
2022	North America	Ford	Fiesta	1200	1100
2022	China	Nissan	Altima	1700	1600
2022	Europe	Hyundai	Elantra	1000	900
2023	North America	Ford	Fiesta	1300	1200
2023	China	Nissan	Altima	1800	1700
2023	Europe	Hyundai	Elantra	1100	1000
2024	North America	Ford	Fiesta	1400	1300
2024	China	Nissan	Altima	1900	1800
2024	Europe	Hyundai	Elantra	1200	1100

I've tried using Above(), and offset the number of rows to get the correct value for each year, however, once I apply filters, the values are incorrect.

The end result would be a line chart, showing YoY growth, that can be filtered by region, make or model.

Thank you!

[JordyWegman]

Hi Joakimnn,

What you need to do is the following:

Table:
Load
 Year &'|'& Region &'|'& Make  &'|'&  Model as %Table
 Year,
 Region,
 Make,
 Model,
 Sales
From [YourSource] (qvd);

Left Join (Table)
Load
 (Year+1) &'|'& Region &'|'& Make  &'|'&  Model as %Table
 Sales
From [YourSource] (qvd);

Now you have joined your sales to the table.

Second option is to go for a mapping load and return 0 if there is no sales for the previous year. In the first solution you will get a null().

Jordy

Climber