Hi all,
I have a solution to this problem but it seems clunky to me. I can't think of a better way of doing it with the data as is however, so looking for suggestions.
I have lookup table with dates and the work level (full time = 100%, half time = 50%, etc.) that team members were working on the team by date. Whenever the work level of a member changed there would be a new row with effective date of the change, the member's name, and the new work level.
I'm trying to find a way to look up the work level on a given date for a given team member.
The table is as follows and you can grab the pbix here.
| Date | Name | Work Level |
| 2018-01-01 | Ron | 0% |
| 2018-01-01 | Trisha | 100% |
| 2018-01-01 | Sam | 100% |
| 2018-01-01 | Vanessa | 80% |
| 2018-01-01 | Brandon | 100% |
| 2018-01-01 | Sarah | 0% |
| 2018-01-01 | Penelope | 50% |
| 2018-01-01 | Jim | 100% |
| 2018-01-01 | Rodger | 100% |
| 2018-05-01 | Penelope | 100% |
| 2018-04-05 | Sarah | 100% |
| 2018-03-19 | Brandon | 0% |
| 2018-10-02 | Brandon | 100% |
| 2018-10-30 | Penelope | 0% |
| 2020-01-24 | Sarah | 0% |
My solution here is hardcoded for Penelope. I'll need to look up each team member on a given date so can't rely on filter context to narrow the table down to a given team member. The approach is to save a table with only rows for the selected team member. Then get the last date from the table, and then finally lookup the work level in the temp table from the table based on the date determined in the previous step.
The reason the active_date was a separate step was that I could not figure out a way to use LASTDATE on the temp table in the same calculate statement that did the lookup. Power BI kept complaining of the syntax, table not found etc. I tried empty table name ('') explicit table/column references but couldn't find another combination that worked.
So the following works, but as I say, seems very clunky. I would appreciate any suggestions to improve this.
Penelope Work Level =
var effective_date = LASTDATE('Work Levels'[Date])
var data1 =
FILTER(
'Work Levels',
'Work Levels'[Name]="Penelope" && 'Work Levels'[Date] <= effective_date
)
var active_date =
CALCULATE(
LASTDATE('Work Levels'[Date]),
data1
)
return
CALCULATE(
MAX('Work Levels'[Work Level]),
FILTER(
data1,
[Date] = active_date
)
)
Cheers,
Blair
