Difference between two values in the came column.

[Anonymous]

Hi there.

I am fairly new to PowerBI and can't seem to get this right.

I want to get the difference between two values in the same column and that will show in a new column.

See data table below

Year	Projected No. of Swimmers	Difference
2021	335196.8
2026	354965.5	+19,768.7
2031	370396.1
2036	383843.7
2041	394566.4
2046	403489.4
2051	412977.8

What would be the DAX for this?

[ryan_mayu]

@Anonymous 

pls try this

Column = 
var last=minx(FILTER('Table (3)','Table (3)'[Projected No. of Swimmers]<EARLIER('Table (3)'[Projected No. of Swimmers])),'Table (3)'[Projected No. of Swimmers])
return 'Table (3)'[Projected No. of Swimmers]-last

if you want the blank for first row. try this

Column = 
var last=minx(FILTER('Table (3)','Table (3)'[Projected No. of Swimmers]<EARLIER('Table (3)'[Projected No. of Swimmers])),'Table (3)'[Projected No. of Swimmers])
return if(ISBLANK(last),blank(),'Table (3)'[Projected No. of Swimmers]-last)

[Anonymous]

Hi there.

Thank you for your reply.

It doesn't seem to be working for me as I am getting a message that "The end of the input was reached".

The table name is Maintain Participation if you could try again using that as the name of the table?

[ryan_mayu]

@Anonymous 

pls see the attachment below

[Anonymous]

Hi @ryan_mayu 

That worked thank you, however it is getting the difference between 2021 and the other years and adding them on top of each other. I just want the difference between each of the years (i.e. 2026-2021, 2031-2026 etc.) I have completed more of the data below if it makes more sense. Sorry if i wasn't clear originally.

See data table below

Year	Projected No. of Swimmers	Difference
2021	335196.8
2026	354965.5	+19,768.7
2031	370396.1	+15,430.6
2036	383843.7	+13,447.6
2041	394566.4
2046	403489.4
2051	412977.8

[ryan_mayu · Accepted solution]

@Anonymous 

sry, typo error in the DAX. pls change minx to maxx

Column =

var last=MAXX(FILTER('Maintain Participation','Maintain Participation'[Projected No. of Swimmers]<EARLIER('Maintain Participation'[Projected No. of Swimmers])),'Maintain Participation'[Projected No. of Swimmers])

return if(ISBLANK(last),blank(),'Maintain Participation'[Projected No. of Swimmers]-last)