DAX Solution for Employee Punch In and Punch Out Times

Objective

To create a DAX calculated table that separates employee punch-in and punch-out times from a given dataset of employee codes and timestamps.

Data Structure

The input dataset consists of the following columns:

• Emp. Code
• Time

Example Input Data

DAX Formula

To achieve the desired output, the following DAX formula will create a calculated table:

PunchTimes = 
VAR SummaryTable = 
    SUMMARIZE (
        'YourDataTable',
        'YourDataTable'[Emp. Code],
        "Punch IN", 
            CALCULATE (
                MIN ( 'YourDataTable'[Time] ),
                ALLEXCEPT ( 'YourDataTable', 'YourDataTable'[Emp. Code] )
            ),
        "Punch OUT", 
            CALCULATE (
                MAX ( 'YourDataTable'[Time] ),
                ALLEXCEPT ( 'YourDataTable', 'YourDataTable'[Emp. Code] )
            )
    )
RETURN
    SummaryTable

Explanations:

1. SUMMARIZE: This function creates a new table by grouping data. The groups are created by the Emp. Code.

2. CALCULATE: This function changes the context in which data is evaluated. In this case, it calculates the minimum and maximum time entries separated by Punch IN and Punch OUT.

3. MIN: Used to find the earliest timestamp for each employee which is recorded as Punch IN.

4. MAX: Used to find the latest timestamp for each employee which is recorded as Punch OUT.

5. ALLEXCEPT: This function maintains the context for the Emp. Code while removing other filters, thus ensuring calculations are done per employee.

Practical Example

Assuming the input data is in a table named EmployeeTimes, executing the above DAX results in a table that displays:

Conclusion

The provided DAX formula accurately separates punch-in and punch-out times based on the employee codes. By using functions like SUMMARIZE, CALCULATE, MIN, and MAX, the solution outlines a structured approach to achieving the desired output within a Power BI context. For further mastery of DAX concepts, consider exploring relevant courses offered on the Enterprise DNA Platform.