Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Irregular salary increase

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).

 

NameDateSalaryCareerStatus
A20-1-2021 €  2.750,00JuniorIN
B5-12-2020 €  3.500,00Senior 
C1-12-2020 Medior 
C20-10-2020 €  3.100,00  
D8-3-2020  OUT
B10-11-2019 €  3.100,00MediorIN
C8-8-2019 €  2.750,00JuniorIN
D5-6-2019 €  2.750,00JuniorIN

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/353980, 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!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

     

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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"
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous , 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's avatar
    Anonymous
    Not applicable
    5 years ago

    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.