How to operate rows of the same column depending on another column

[amatasa]

Hello community,

I would like to operate rows of the same column depending on another column. Here you have an example:

SalesTable:

Country                  Year                  Sales           

Spain                     2018                  20.000

Spain                     2019                  24.000

France                   2018                  18.000

France                   2019                  16.000

What I want to see is the difference of sales between years for each country: Spain: 4.000; France: -2.000.

How should I do that?

Thank you very much in advanced! 

[parry2k · Accepted solution]

@amatasa logically you shoud have date dimension in your model to do this kind of calculation but you can use following dax to perform this, drop country, year and this measure in a table visual

Sales Diff = 
VAR sales = SUM( Table7[Sales] )
VAR prevyear  =CALCULATE( MAX( Table7[Year] ), ALLEXCEPT( table7, Table7[Country] ) ) - 1
VAR pySales = CALCULATE( sum( Table7[Sales] ), 
Table7[Year] = prevyear ) 
RETURN sales - pySales