LOOKUPVALUE with date comparison

Hi VHosamane,

I've split this into two parts - a calculated column that looks up the rate, and then a measure that will calculate your desired result, as I figured this would make the business logic easier to modify, should you want to.

I manually replicated your model, so I think everything's named as per your example, but you may need to tweak for any typos.

Derived Rate Calculated Column

It looks like you're using a default rate of 9 if a value can't be found, so I've declared this up-top.

Derived Rate = 
    /* Use if lookup doesn't bring anything back - you might want to model this separately later */
        VAR DefaultRate = 9

    /* Current Staff Member */
        VAR StaffMember = StarActualsMonthlyAggregated[Staff_Member]

    /** Filter the ContractorList table by staff member, and check timesheet date falls in ranges.
        Assumes that ContractorList is disconnected to StarActualsMonthlyAggregated.
        If we aren't specifying an end date in the column then we need to ensure the BLANK() value
        is handled accordingly.
    */        
        VAR Matches = FILTER(
                ContractorList,
                ContractorList[StaffName] = StaffMember
                && StarActualsMonthlyAggregated[TimesheetDate] >= ContractorList[StartDate]
                && (StarActualsMonthlyAggregated[TimesheetDate] <= 
                        ContractorList[EndDate] || ContractorList[EndDate] = BLANK())
            )

    /* In case we get multiple matches, take the max value as our rate, falling back to the default if
       we didn't find anything */
        VAR CalculatedRate = MAXX(Matches, [Rate])

RETURN
    
    IF(ISBLANK(CalculatedRate), DefaultRate, CalculatedRate)

Monthly Resource Cost Measure

Monthly Resource Cost = 
    SUMX(
        StarActualsMonthlyAggregated,
        StarActualsMonthlyAggregated[MonthlyInputHrs] * StarActualsMonthlyAggregated[Derived Rate]
    )

Validation

Here's how my data looks in Power BI - I've added in an anomalous timesheet entry, to confirm that default rate is used if we can't look anything up:

Example, showing unknown contractor timesheet using default rate

Here's another example, showing what happens if we remove the second ContractorList record for Jim (showing a lookup of the default rate for Feb):

If we remove Jim's rates effective Feb, then he'll fall-back to the default rate

Hopefully this is all you need. Good luck!

Daniel

If my post helps, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂