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,

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's avatar
Zeshansaif
Frequent Visitor
1 year ago

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

 

  • Zeshansaif's avatar
    Zeshansaif
    Frequent Visitor
    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