Forum Discussion
Irregular salary increase
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.
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.
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 😊
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/49734I 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) )