Y-o-Y change when year is available as a whole number ONLY

[Will_Ryu]

Would appreciate your guidance in getting Y-o-Y change when the Year is available as a whole number only, like so 2018, 2019 etc. And no  there is no Date column in the base data or a Year and Month column (from which a Date column can then be created). This is the data provided.

As you are alI aware, it is futile to convert that column into a Date column as it converts to {9 -12 July 1905}

If the sales year is region dependant and not calendar year based will it make a difference?

Since I am unable to upload a file, I have attached a table

Sales Year (Whole Number)	Sales Amount	COGS
2017	11,928,556	6,889,539
2018	30,516,892	14,395,851
2019	42,895,110	21,022,703
2020	24,468,717	14,096,441

Thanks in advance, W

[v-yangliu-msft · Accepted solution]

Hi @Will_Ryu ,

Here are the steps you can follow：

1. Create measure.

Last Year =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Sales Year]=MAX('Table'[Sales Year])-1),[Sales Amount])

LastY-CurrentY% =
DIVIDE(
    [Last Year],
SUMX(
    FILTER(ALL('Table'),
    'Table'[Sales Year]=MAX('Table'[Sales Year])),[Sales Amount])
    )

2. Result:

If you need pbix, please click here.

Y-o-Y change when year is available as a whole number ONLY.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[HoangHugo]

Hi

I understand you want to calulate Last year sales to define Y-o-Y with the column of Sales year. Please try this

Total_sale = SUM(Amount)

Last_year_sale = CALCULATE (Total_sale,'[Sales Year] -1)

Y-o-Y = (Total_sale-Last_year_sale)/Last_year_sale

[Will_Ryu]

Thank you Hugo

However, using your method, I got the following result

Sales Year	Sales Amount	LY	Y-o-Y
2017	11928556	109809275	-89.14%
2018	30516892	109809275	-72.21%
2019	42895110	109809275	-60.94%
2020	24468717	109809275	-77.72%

[HoangHugo]

Hi

Please try modify the measure Last_year_sale

Last_year_sale =

 var year = MAX (Sales year)

 return CALCULATE (Total_sale,year -1)

[MahyarTF]

Hi,

If I am not mistaken you are looking for the Year to Year changes. For this purpose :

1- In Power Query I created the Index column in the particular table :

2- Then in Power BI, created the below Column :

_Previous Sales Amount = LOOKUPVALUE( Sheet130[Sales amount], Sheet130[Index], Sheet130[Index] -1)

3-Then this measure :

_Y-o-Y Changes = if( CALCULATE(sum(Sheet130[_Previous Sales Amount])) > 0 ,

                     CALCULATE(sum(Sheet130[Sales amount])) - CALCULATE(sum(Sheet130[_Previous Sales Amount]))

                     )

4- And then use this column/measure in visual :

Appreciate your Kudos

[Will_Ryu]

@MahyarTF  Your solution works great on the simple table with one entry for each year but I wasn't able to extend it to  this data set. 

Sales Year	Country	Sales Amount
2018	Australia	2,699,691
2018	Canada	3,704,560
2018	France	456,704
2018	Germany	552,620
2018	United Kingdom	593,106
2018	United States	15,854,209
2019	Australia	2,221,468
2019	Canada	5,908,467
2019	France	2,119,601
2019	Germany	663,592
2019	United Kingdom	2,191,569
2019	United States	20,965,411
2020	Australia	5,734,177
2020	Canada	6,742,743
2020	France	4,675,251
2020	Germany	3,662,088
2020	United Kingdom	4,886,046
2020	United States	26,177,970