Equivalent of SQL Inner Query in DAX for Employee Mutation

Hi pelangi 

You can try this measure. If the employee are in two positions in a week (when the start date is not a week start date), it will display both positions in that week. _previousPosition and _nextPosition variables represent the two positions. You can modify the returned result per your need. 

Department Position =
VAR _weekStartDate = MIN ( 'Date'[Date] )
VAR _weekEndDate = MAX ( 'Date'[Date] )
VAR _onlyPosition =
    MAXX (
        FILTER (
            Employee,
            Employee[StartDate] <= _weekStartDate
                && (
                    _weekEndDate <= Employee[FinishDate]
                        || ISBLANK ( Employee[FinishDate] )
                )
        ),
        Employee[Department]
    )
VAR _previousPosition =
    MAXX (
        FILTER (
            Employee,
            _weekStartDate <= Employee[FinishDate]
                && _weekEndDate > Employee[FinishDate]
        ),
        Employee[Department]
    )
VAR _nextPosition =
    MAXX (
        FILTER (
            Employee,
            _weekStartDate < Employee[StartDate]
                && _weekEndDate >= Employee[StartDate]
        ),
        Employee[Department]
    )
RETURN
    IF (
        NOT ( ISBLANK ( _previousPosition ) ),
        _previousPosition & "," & _nextPosition,
        _onlyPosition
    )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.