Forum Discussion
LOOKUPVALUE with date comparison
I need help with calcuating MonthlyResouceCost keeping into account of the Start and End date. I have a simple formula below which I was using but was not accounting for StartDate and EndDate from ContractList table. Both there two tables, StarActualsMonthlyAggregated & ContractorList are not linked.
Any help is greatly appreciated.
I have a formula for calculating the MonthlyResourceCost, but without start and date taking into consideration.
MonthlyResourceCost =
VAR varLookup =
LOOKUPVALUE(
ContractorList[Rate], ContractorList[StaffName], StarActualsMonthlyAggregated[Staff_Member]
)
RETURN
IF(
varLookup <> BLANK(),
StarActualsMonthlyAggregated[MonthlyInputHrs] * varLookup,
StarActualsMonthlyAggregated[MonthlyInputHrs] * 9
)
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 🙂
2 Replies
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 🙂
👍Very nice explanation & comments. The calculated column funtion worked perfectly. I had to tweak the measure, but it was on the right path. So thank you very much. 👏
