Power Pivot - Looking up details with start and end dates?

0
votes

I need help in the formula correctness of my approach in this PowerPivot table in Excel.

I have two tables: daily timesheet and employee details. The idea is to lookup the department, sub-department, and managers from the employee_details table based on the employee number and shift date in the daily_timesheet table.

Here's a representation of the tables:

daily_timesheet

shift_date emp_num scheduled_hours actual_worked_hrs dept sub_dept mgr
2022-02-28 01234 7.5 7.34 16100 16181 05432
2022-03-15 01234 7.5 7.50 16200 16231 06543

employee_details

emp_num dept_code sub_dept_code mgr_emp_num start_date end_date is_current
01234 16000 16041 04321 2022-01-01 2022-01-31 FALSE
01234 16100 16181 05432 2022-02-01 2022-01-28 FALSE
01234 16200 16231 06543 2022-03-01 null TRUE

End dates have null values if it is the employee's current assignment; but it's never null if the is_current field is FALSE.

Here's what I've managed so far:

  1. First, lookup the current start date in the employee details is less than or equal to the shift date.
  2. If true, I'm using the LOOKUP function to return the department, sub-department, and manager by searching the employee number and the true value in the is_current field.
  3. Else, I use the MIN function to get the value of those fields and wrap it around a CALCULATE function then apply FILTER for: (1) emp_num matching the timesheet, (2) is_current that has a FALSE value, (3) start_date less than or equal to the shift_date, and (4) end_date is greater than or equal to the shift_date.

And the bedrock of my question is actually, the item 3 above. I know using the MIN function is incorrect, but I can't find any solution that will work.

Here's the formula I've been using for to get the dept in the daily_timesheet table from the employee_details table:

=IF(
   LOOKUP(employee_details[start_date], 
          employee_details[emp_num], 
          daily_timesheet[emp_num], 
          employee_details[is_current] = TRUE) <= daily_timesheet[shift_date],
   LOOKUP(employee_details[dept_code], 
          employee_details[emp_num], 
          daily_timesheet[emp_num], 
          employee_details[is_current] = TRUE),
   CALCULATE(MIN(employee_details[dept_code]),
          FILTER(employee_details, employee_details[emp_num] = daily_timesheet[emp_num]),
          FILTER(employee_details, employee_details[is_current] = FALSE),
          FILTER(employee_details, employee_details[start_date] <= daily_timesheet[shift_date]),
          FILTER(employee_details, employee_details[end_date] >= daily_timesheet[shift_date]))
)

Any advice please?

daxpowerpivot