Irregular salary increase

[Anonymous]

Hi all!

I am fairly new to Power BI and I'm not really used to all the formulas. Up until now I could work around it, but now I do need some sophisticated solution. 

I have this sample table, which shows mutations (they aren't regular) in someones salary and career, that are not necessarily linked (see Name C) and not everyone starts as a junior (see Name B).

Name	Date	Salary	Career	Status
A	20-1-2021	€  2.750,00	Junior	IN
B	5-12-2020	€  3.500,00	Senior
C	1-12-2020		Medior
C	20-10-2020	€  3.100,00
D	8-3-2020			OUT
B	10-11-2019	€  3.100,00	Medior	IN
C	8-8-2019	€  2.750,00	Junior	IN
D	5-6-2019	€  2.750,00	Junior	IN

I have a separate calendar table.

I need the following information for a yearly report (with a slicer):

• How many people have had a raise 
• The percentual change in salary per person that had a raise
• How many people were promoted
• The current ... of everyone (I need these values in another table, but also per year)
  • Salary
  • Career
  • Status 

I think for every question, I need to look down in this order (I hope my notation makes sense):

1. Name = CurrentName
2. Date < CurrentDate

And do things with measures, CALCULATE and FILTER. I have tried so many things, but I get errors all the time and I do not have a feeling yet for these things. I have already looked at posts like this one https://community.powerbi.com/t5/Desktop/Year-over-Year-salary-increase-for-each-employee/td-p/35398... but this example has regular intervals and doesn't have empty value. 

Please tell me if my information is unclear or when you need more information. If you can help me with one question I am already really thankful!

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

I tried your measure, your measure will still show values in D (Status = OUT) when currentdate >Date.

Your measure:

Current Salary = 
var CurrName = LASTNONBLANK('Table'[Name],[Name])
return

var LastSalDate = 
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        'Table',
        'Table'[Date] <= MAX('Calendar'[Date]) &&
        'Table'[Name] = CurrName &&
        'Table'[Salary] > 0)
)
return

LOOKUPVALUE(
    'Table'[Salary],
    'Table'[Name],
    CurrName,
    'Table'[Date],
    LastSalDate
)

Result:

CurrentDate = 2021/12/31, D should Out the company, but we still get the result.

Please try my measure:

Measure = 
VAR _SelectDate = MAX('Calendar'[Date])
VAR _LastDate = MAXX(FILTER('Table','Table'[Date]<=_SelectDate),'Table'[Date])
VAR _LastStatus = CALCULATE(MAX('Table'[Status]),FILTER('Table','Table'[Date] = _LastDate))
VAR _LastSalary = CALCULATE(SUM('Table'[Salary]),FILTER('Table','Table'[Date] = _LastDate&&_LastStatus<>"OUT"))
VAR _LastDateNoBlank = MAXX(FILTER('Table','Table'[Date]<=_SelectDate&&'Table'[Salary]<>BLANK()),'Table'[Date])
VAR _LastSalaryNoblank = CALCULATE(SUM('Table'[Salary]),FILTER('Table','Table'[Date] = _LastDateNoBlank))
Return
IF(_LastStatus = "OUT",BLANK(),IF(_LastSalary= BLANK(),_LastSalaryNoblank,_LastSalary))

Result is as below.

CurrentDate = 2021/12/31

CurrentDate = 2019/11/10

Best Regards,

Rico Zhou

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

[Anonymous]

I have figured out a part of my question myself! 
To find the last date per person that had a value for Salary, I did this:

FirstNextSalaryDate = 
IF(NL_Mutations[Salary]>0,
CALCULATE(
    MAX(NL_Mutations[Date]),
    FILTER(
        NL_Mutations,
        NL_Mutations[Name] = EARLIER(NL_Mutations[Name]) &&
        NL_Mutations[Salary]>0 &&
        NL_Mutations[Date] < EARLIER(NL_Mutations[Date])
        )
    )
)

This worked perfectly. Then I used this column to calculate the SalaryIncrease:

SalaryIncrease = 
IF(
    NOT(ISBLANK(NL_Mutations[FirstNextSalaryDate])),
    NL_Mutations[Salary]-LOOKUPVALUE(
        NL_Mutations[Salary],
        NL_Mutations[Name],
        NL_Mutations[Name],
        NL_Mutations[Date],
        NL_Mutations[FirstNextSalaryDate]),
    BLANK()
)

Furthermore, I forgot to say that I already knew who had a raise, but I need to compare them to the people whose Status was IN at the start of the chosen year in the slicer. Thus the only question that still stands is how to make a measure for the status.

Raise = 
IF(
    NOT(ISBLANK(NL_Mutations[SalaryIncrease])),
    "Yes",
    "No"
    )

[v-rzhou-msft]

Hi @Anonymous 

Could you show me your calculate logic to calculate by date slicer?

In your sample:

Why C's salary in 2020/12/1 is blank? And all status except first date(2019/08/08) are blank. Does rasie and  promote have the same meaning?

If I have a date slicer from 2019/01/01 to 2021/01/01, please show me a sample like when you select date = xxxx, and you want to get the result like xxx. It is better for you to show me a screenshot.

Best Regards,

Rico Zhou

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

[Anonymous]

Hi @v-rzhou-msft , thanks for your reply!

Raise and promotion is something different. Raise is an increase in salary, promotion is when you get a new position. Often this is linked, but not always. The status indicates when someone started working at the company or when someone left.

I do not fully understand what you're asking, but this is how I want it to look like for the salary when I would have a table and the slicer set on 2020:

I have figured out part of the solution for the salary I think, but I still got a few problems. This is my code:

Current Salary = 
var CurrName = LASTNONBLANK(Table[Name],[Name])
return

var LastSalDate = 
CALCULATE(
    MAX(Table[Date]),
    FILTER(
        Table,
        Table[Name] = CurrName &&
        Table[Salary] > 0)
)
return

LOOKUPVALUE(
    Table[Salary],
    Table[Name],
    CurrName,
    Table[Date],
    LastSalDate
)

The problems I've found so far are:

• When the last mutation was a difference in salary, it doens't show and takes the last salary before
• When someone hasn't got a salary mutation in 2020, it doesn't show the person at all in the list, but it should show the person's last salary from for instance the year before.

Additonally, I would like a way to only show people whose status wasn't OUT at the start of that year, because that they weren't working at the company that year. But first I need to fix the points metioned above. Hopefully you can help me 😊

[Anonymous]

I solved my two problems!

Well, actually the first one didn't exist, I mislooked. But for the second one, this helped 
https://community.powerbi.com/t5/Desktop/Ignore-Slicer-in-measure/td-p/49734

I deleted all the relationships with the calendar and changed the calculate part to:

CALCULATE(
    MAX(Table[Date]),
    FILTER(
        Table,
        Table[Date] <= MAX(Calendar[Date]) &&
        Table[Name] = CurrName &&
        Table[Salary] > 0)
)