Help with DAX Measure for Calculating Employee Workdays by Year

[Zeshansaif1]

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

1. If hired and terminated in the same year:

   • Calculate Termination Date - Hiring Date

2. If termination occurs in a different year:

   • Add 365 days for full years worked.

   • For the year of termination, calculate Termination Date - Start of the Year.

3. If not terminated:

   • Add 365 days for all years except the hiring year.

   • For the hiring year, calculate End of Year - Hiring Date.

My relationships:

• Active relationship between Date[Date] and DataTable[HireDate Updated].

• Inactive relationship between Date[Date] and DataTable[TerminationDate].

Here’s the measure I came up with with the help of ChatGPT

5. Total Days Worked =

VAR SelectedYear = SELECTEDVALUE ( 'Date'[Year] )

RETURN

SUMX (

'DataTable',

IF (

YEAR ( 'DataTable'[HireDate Updated] ) > SelectedYear || (

NOT ( ISBLANK ( 'DataTable'[TerminationDate] ) )

&& YEAR ( 'DataTable'[TerminationDate] ) < SelectedYear

), 0,

DATEDIFF (

MAX ( 'DataTable'[HireDate Updated], DATE ( SelectedYear, 1, 1 ) ),

MIN (

IF (

ISBLANK ( 'DataTable'[TerminationDate] ),

DATE ( SelectedYear, 12, 31 ),

'DataTable'[TerminationDate]

),

DATE ( SelectedYear, 12, 31 )

), DAY ) + 1 ) )

The problem is that this measure works correctly for employees hired and terminated within the same year but not the employees hired in earlier years.

Any advice on fixing this or a better approach? Let me know if more details are needed!
I am attaching the file here. Also why is there blank in year slicer?

Power BI File - MegaLink 

Thanks in advance! 🙏

[Anonymous]

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]

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

)

[rajendraongole1]

Hi @Zeshansaif1 - Your measure logic needs to handle employees who were hired in earlier years correctly. The issue arises because you are not accounting for the fact that employees hired before the selected year need their working days calculated differently.

Total Days Worked =
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
VAR StartOfYear = DATE(SelectedYear, 1, 1)
VAR EndOfYear = DATE(SelectedYear, 12, 31)

RETURN
SUMX(
'DataTable',
VAR HireDate = 'DataTable'[HireDate Updated]
VAR TerminationDate = 'DataTable'[TerminationDate]
VAR IsTerminated = NOT(ISBLANK(TerminationDate))
VAR DaysWorked =
SWITCH(
TRUE(),
-- Hired and terminated in the same year
YEAR(HireDate) = SelectedYear && IsTerminated && YEAR(TerminationDate) = SelectedYear,
DATEDIFF(HireDate, TerminationDate, DAY) + 1,

-- Hired before the selected year and terminated in the selected year
YEAR(HireDate) < SelectedYear && IsTerminated && YEAR(TerminationDate) = SelectedYear,
DATEDIFF(StartOfYear, TerminationDate, DAY) + 1,

-- Hired in the selected year and terminated in a later year
YEAR(HireDate) = SelectedYear && IsTerminated && YEAR(TerminationDate) > SelectedYear,
DATEDIFF(HireDate, EndOfYear, DAY) + 1,

-- Hired before the selected year and terminated after the selected year
YEAR(HireDate) < SelectedYear && IsTerminated && YEAR(TerminationDate) > SelectedYear,
DATEDIFF(StartOfYear, EndOfYear, DAY) + 1,

-- Hired before the selected year and not terminated
YEAR(HireDate) < SelectedYear && NOT(IsTerminated),
DATEDIFF(StartOfYear, EndOfYear, DAY) + 1,

-- Hired in the selected year and not terminated
YEAR(HireDate) = SelectedYear && NOT(IsTerminated),
DATEDIFF(HireDate, EndOfYear, DAY) + 1,

0 -- Default case
)
RETURN
DaysWorked
)

This ensures the measure does not calculate for blank years. Hope this works. please check.

[Zeshansaif]

Thankyou for your efforts. However It's not working as intended. 

I came up with a solution that does work.

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

)