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

     

     

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

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