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!

Job Level	Salary	Earlier Job Level Salary	Difference $	Difference %
Admin/Coordinator	$32,325
Associate	$58,156	$32,325	$25,831	80%
Senior Associate	$69,437	$58,156	$11,281	19%
Manager	$100,687	$69,437	$31,249	45%
Senior Manager	$143,626	$100,687	$42,939	43%

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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!