Forum Discussion

Zeshansaif1's avatar
Zeshansaif1
New Member
1 year ago

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
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Zeshansaif1 ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

 

 

Best Regards

Yilong Zhou

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

  • Zeshansaif's avatar
    Zeshansaif
    Frequent Visitor
    1 year ago

    This is the solution. It is giving accurate total numbers. I can't mark this as solution as this account's email address has been mapped to different account. Because of which I lost access to mark it as a solution.


    5.1 Days worked (Correct) = 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
    )