Difference in Salary from Previous Job Level

[stbrooks13]

I have these job levels (in order) with their corresponding average salaries, earlier job level salaries, difference in $ and % between the two. This is a manual table, and I need a DAX formula to calculate the earlier job level salary so that I can calculate the differences, but I can't for the life of me figure out the formula. Please help!

[TABLE="width: 547"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Job Level[/TD]
[TD]Salary[/TD]
[TD]Earlier Job Level Salary[/TD]
[TD]Difference $[/TD]
[TD]Difference %[/TD]
[/TR]
[TR]
[TD]Admin/Coordinator[/TD]
[TD="align: right"]$32,325[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Associate[/TD]
[TD="align: right"]$58,156[/TD]
[TD="align: right"]$32,325[/TD]
[TD="align: right"]$25,831[/TD]
[TD="align: right"]80%[/TD]
[/TR]
[TR]
[TD]Senior Associate[/TD]
[TD="align: right"]$69,437[/TD]
[TD="align: right"]$58,156[/TD]
[TD="align: right"]$11,281[/TD]
[TD="align: right"]19%[/TD]
[/TR]
[TR]
[TD]Manager[/TD]
[TD="align: right"]$100,687[/TD]
[TD="align: right"]$69,437[/TD]
[TD="align: right"]$31,249[/TD]
[TD="align: right"]45%[/TD]
[/TR]
[TR]
[TD]Senior Manager[/TD]
[TD="align: right"]$143,626[/TD]
[TD="align: right"]$100,687[/TD]
[TD="align: right"]$42,939[/TD]
[TD="align: right"]43%[/TD]
[/TR]
</tbody>[/TABLE]

 

[citizenbh]

Look at the file in Excel:

[URL]https://www.dropbox.com/s/0s7ibofb9j61x0v/Difference%20in%20Salary%20from%20Previous%20Job%20Level.xlsx?dl=0[/URL]

 

[stbrooks13]

Thank you, what formula did you use for earlier JLS?

 

[citizenbh]

Dowload file and open PowerPivot for Excel
Formulas are:

Earlier_JLS=CALCULATE(VALUE(MAX(Table1[Salary])),FILTER(Table1,Table1[Index]=EARLIER(Table1[Index])-1))
Difference $=IF(Table1[Salary]=Table1[Salary]-Table1[Earlier_JLS],0,Table1[Salary]-Table1[Earlier_JLS])
Difference %=IF(Table1[Difference $]=0,0,Table1[Salary]/Table1[Earlier_JLS]-1)

 

[stbrooks13]

Dowload file and open PowerPivot for Excel
Formulas are:

Earlier_JLS=CALCULATE(VALUE(MAX(Table1[Salary])),FILTER(Table1,Table1[Index]=EARLIER(Table1[Index])-1))
Difference $=IF(Table1[Salary]=Table1[Salary]-Table1[Earlier_JLS],0,Table1[Salary]-Table1[Earlier_JLS])
Difference %=IF(Table1[Difference $]=0,0,Table1[Salary]/Table1[Earlier_JLS]-1)

This is perfect, thank you! I was looking for a measure instead of a calculate column. But it works perfectly!