Subtracting two columns of same table

[archuleta28]

Hi All, 

I need to subtract two columns from same table. I'll try to explain with example :

Lets say, I have this table in which,  % of costs to sales - is a calculated column.

Formula is: % of costs to sales = DIVIDE('Table'[Costs],'Table'[Sales], 0)

Department	Costs	Sales	Year	% of  Costs to Sales
a	34090	3124235	2021-22	1.09%
b	3453	532522	2022-23	0.65%
a	2424	234234235	2021-22	0.00%
b	53462	24324221	2022-23	0.22%
a	234324	42345235	2021-22	0.55%
b	25251	323424	2021-22	7.81%

When I add a matrix, it will give me:

Here, I have added year in Columns, Departments in Row and calculated % in Values.

Now, I need to calculate difference of % values between columns - 2022-23 and 2021-22. 

That means-

For deartment A,  I should get  -1.65% in new column and

For deartment B, I shuld get -6.94%.

Can some one help me, how can I calculate this difference? Any help is much appreciated!:)

[v-xinruzhu-msft · Accepted solution]

Hi @archuleta28 

You can refer to the following column:

% of  Costs to Sales = var a=SUMMARIZE(FILTER('Table',[Year]<>"Difference"),[Department],[Costs],[Sales],[Year])
var _current_=SUMX(FILTER(a,[Department]=EARLIER('Table'[Department])&&[Year]=MAXX(a,[Year])),DIVIDE([Costs],[Sales]))
var _last=SUMX(FILTER(a,[Department]=EARLIER('Table'[Department])&&[Year]=MINX(a,'Table'[Year])),DIVIDE([Costs],[Sales]))
return IF('Table'[Year]="Difference",_current_-_last,DIVIDE([Costs],[Sales]))

Output:

Best Regards!

Yolo Zhu

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

[FreemanZ]

hi @archuleta28 

try to create a measure like:

Diff = 

VAR _year = MAX('Table'[Year])

VAR _py = LEFT(_year, 2)&MID(_year, 3, 2)-1&"-"&RIGHT(_year, 2)-1

VAR _value =

CALCULATE([% of costs to sales],  'Table'[Year] = _py)

RETURN

[% of costs to sales] - _value