Filtered Table

[newhopepdx]

tblSalaryModifications records list all changes to an employee's salary or hourly rate. I'm trying to create a measure that will yield the annual Wage for the (slicer) selected emplolyee, by (additionally) filtering the table down to the most recent salary change.

The measure below errors out saying that the table in the VAR curEmp_ is "not available". Where have I gone wrong?

Est Annual Wages =

VAR lastedate_ = MAX( 'tblSalaryModifications'[DtAssigned] )

VAR curEmp_ = FILTER( 'tblSalaryModifications', 'tblSalaryModifications'[DtAssigned] = lastedate_ )

VAR estAnnualWage_ =

    IF( curEmp_[Salary] > 0,

    curEmp_[Salary],

    curEmp_[HoursPerWeek] * curEmp_[HourlyRate] * 52

    )

    RETURN estAnnualWage_

[amitchandak]

@newhopepdx , Follow the code of lastest given here

Last Qty = Var _max = maxx(filter( ALLSELECTED('tblSalaryModifications'), 'tblSalaryModifications'[EMPID] = max('tblSalaryModifications'[EMPID] )), 'tblSalaryModifications'[DtAssigned])
return
CALCULATE(sumx('tblSalaryModifications',[HoursPerWeek] *[HourlyRate] * 52 ), filter( ('tblSalaryModifications'), 'tblSalaryModifications'[EMPID] = max('tblSalaryModifications'[EMPID]) && 'tblSalaryModifications'[Date] =_max))

Sum Last Qty = sumx(VALUES('tblSalaryModifications'[EMPID]) , [Last Qty])

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[newhopepdx · Accepted solution]

Your code gave me the prodding I needed to work out the solution.

Here's the code that actually works:

Est Annual Wages =

VAR _lastChangetbl = FILTER(

        ALLSELECTED('tblSalaryModifications'),

        'tblSalaryModifications'[DtAssigned] = MAX('tblSalaryModifications'[DtAssigned] ))

VAR _salary = SUMX( _lastChangetbl, [Salary])

VAR _annualHourly = SUMX( _lastChangetbl, [HourlyRate] * [HoursPerWeek] ) * 52

RETURN

    IF(

        _salary > 0,

        _salary,

        _annualHourly

    )