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.