Forum Discussion
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...
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)RETURNSUMX(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)
Thankyou, however if i remove the relationship then other visuals will be impacted. Is there anyother way?
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
)