Help with DAX Measure for Calculating Employee Workdays by Year

[Zeshansaif]

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! IPower BI File 

Thanks in advance! 🙏

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file without having the relationship between the calendar table and the fact table.

working days: = 
VAR _hiringdate =
    MAX ( employee[hiring_date] )
VAR _terminationdate =
    MAX ( employee[termination_date] )
VAR _t01 =
    FILTER (
        'calendar',
        'calendar'[Date] >= _hiringdate
            && 'calendar'[Date] <= _terminationdate
    )
VAR _t02 =
    FILTER ( 'calendar', 'calendar'[Date] >= _hiringdate )
RETURN
    IF (
        HASONEVALUE ( employee[employee_id] ),
        SWITCH (
            TRUE (),
            _terminationdate = BLANK (), COUNTROWS ( _t02 ),
            COUNTROWS ( _t01 )
        )
    )

[Zeshansaif]

Thankyou, however if i remove the relationship then other visuals will be impacted. Is there anyother way?

[Jihwan_Kim]

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]

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 

[Zeshansaif · Accepted solution]

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

)