Forum Discussion

Zeshansaif's avatar
Zeshansaif
Frequent Visitor
1 year ago
Solved

Help with DAX Measure for Calculating Employee Workdays by Year

Hello everyone, I’m trying to calculate the total number of days each employee worked in a given year (from 2020 to 2024) using a year slicer. Here is the logic If hired and terminated in the sam...
Need Help
  • Zeshansaif's avatar
    Zeshansaif
    1 year ago

    Found the solution. Thanks for the suggestion of Inactive relationship.

    Total Days Worked =



    VAR SelectedYear = SELECTEDVALUE('Date'[Year])
    VAR _StartOfYear = DATE(SelectedYear, 1, 1)
    VAR _EndOfYear = DATE(SelectedYear, 12, 31)
    RETURN
    SUMX(
        FILTER(
            ALL('DataTable'),
            'DataTable'[HireDate Updated] <= _EndOfYear &&
            (ISBLANK('DataTable'[TerminationDate]) || 'DataTable'[TerminationDate] >= _StartOfYear)
        ),
        DATEDIFF(
            MAX('DataTable'[HireDate Updated], _StartOfYear),
            MIN(
                IF(
                    ISBLANK('DataTable'[TerminationDate]),
                    _EndOfYear,
                    'DataTable'[TerminationDate]
                ),
                _EndOfYear
            ),
            DAY
        ) + 1
    )
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
1 year ago

Hi,

If it is OK, please share your sample pbix file's link, and then I can try to look into it.

Thank you.

Zeshansaif's avatar
Zeshansaif
Frequent Visitor
1 year ago

I am attaching the PBIX again. I got the measure to work correctly by using All and Filter. Both measures are in this file.
PBIX