Calculating the Latest Salary for Salary Banding

[GJ217]

Hi,

I need to calculate the latest salary for Active and Inactive employees in order to present them in Salary Bands.

I have a Person table which lists the employment start and end date for each employee. I also have a Salary table which lists the salaries for each person for each year there was a change (increase or decrease) and this table has a SalaryFromDate and a SalaryToDate.

The Salary table is linked to the Person table via the Person Number in a many to many relationship (this is because we have many employees who leave and come back).

How do I get the latest Salary for both Active employees and Inactive employees?

Any and all suggestions with this is much appreciated.

[v-cgao-msft]

Hi @GJ217 ,

Suppose we have:

Person Table

Salary Table

Relationships

Please  try this:

Measure = 
VAR _max_start_date = MAX('Person Table'[start date])
VAR _salary = CALCULATE(SUM('Salary Table'[salaries]),'Salary Table'[SalaryFromDate]>=_max_start_date) 
RETURN
_salary

Result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[GJ217]

Hi @v-cgao-msft 

Thanks for getting back to me on this. The measure totals up the salaries an employee has had in my visual and my tables are joined in the same way as your sample. 

I think the SUM('Salary Table'[salaries]), part of the measure is totaling up all the salaries.

Is there a way to tweak this measure to bring back the lastest salary?

[v-cgao-msft · Accepted solution]

Hi @GJ217 ,

Please try:

Measure = 
VAR _max_date = MAX('Salary Table'[SalaryFromDate])
VAR _salary = CALCULATE(MAX('Salary Table'[salaries]),'Salary Table'[SalaryFromDate]>=_max_date) 
RETURN
_salary

If this does not work, please feel free to contact me and preferably with expected output.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[GJ217]

@v-cgao-msft   this works, thank you very much.