Assign Department from Audit Table

Hi, pedanticpad 

Based on your data, you may create two calculated columns in 'Clock' table as follows.

COSTCODE = 
CALCULATE (
    MAX ( 'Audit'[NewValue] ),
    FILTER (
        'Audit',
        'Audit'[UserID] = 'Clock'[UserID]
            && 'Audit'[ChangeType] = "COSTCODE"
            && 'Audit'[ChangeDate]
                = CALCULATE (
                    MAX ( 'Audit'[ChangeDate] ),
                    FILTER (
                        'Audit',
                        'Audit'[ChangeDate] <= 'Clock'[Date]
                            && 'Audit'[UserID] = 'Clock'[UserID]
                    )
                )
    )
)

DEPARTMENT = 
CALCULATE (
    MAX ( 'Audit'[NewValue] ),
    FILTER (
        'Audit',
        'Audit'[UserID] = 'Clock'[UserID]
            && 'Audit'[ChangeType] = "DEPARTMENT"
            && 'Audit'[ChangeDate]
                = CALCULATE (
                    MAX ( 'Audit'[ChangeDate] ),
                    FILTER (
                        'Audit',
                        'Audit'[ChangeDate] <= 'Clock'[Date]
                            && 'Audit'[UserID] = 'Clock'[UserID]
                    )
                )
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.