Measure to calculate number of employed days in period

Anonymous — Fri, 14 Apr 2023 09:19:36 GMT

Hi there

Love PowerQuery, but for some reason I can't get my head around DAX.

Anyway - I have an Employee table where I have an employment start and termination date listed. For any given period I'd like to calculate the number of days the worker has been employed, taking the start and termination dates into account.

So far I have the below, but a few issues...
1) It complains about my True/False expression. What am I doing wrong here?
2) How can I deduct 1 day from STDATE_EMP (when used in EXCL_DAYS)? Tried using DATEADD but it was expecting a column?

3) The SELECTEDRANGE is right now a single column table. Can I somehow filter this? I'm thinking a combination of VALUES and FILTER...?

Sorry that's a lot but really struggling.

WORKERS_DAYS =

VAR STDATE = CALCULATE(MIN(Dates[Date]))

VAR NDDATE = CALCULATE(MAX(Dates[Date]))

-- SINGLE COLUMN TABLE WITH ALL DATES IN MONTH:

VAR SELECTEDRANGE = CALENDAR(STDATE, NDDATE)

-- WORKERS ACTUAL STARTING DATE

VAR STDATE_EMP = CALCULATE(MIN('NAV - Employee'[Employment Date]))

-- DECIDE IF WORKER STARTED AFTER THE PERIODS START DATE

VAR LATESTART_EMP = STDATE_EMP > STDATE

-- SINGLE COLUMN TABLE WITH POSSIBLE DATES TO EXCLUDE

VAR EXCL_DAYS = CALENDAR(STDATE, STDATE_EMP)

-- EXCLUDE DAYS FROM SELECTEDRANGE

VAR EXCLUDEDDAYS = IF(LATESTART_EMP, EXCEPT(SELECTEDRANGE, EXCL_DAYS), SELECTEDRANGE)

RETURN CALCULATE(COUNTROWS(), EXCLUDEDDAYS)

Re: Measure to calculate number of employed days in period

johnt75 — Fri, 14 Apr 2023 10:01:40 GMT

The below assumes that you have a column called Is Working Day in your date table, which contains a 1 if it is a working day and 0 otherwise.

Employed days = SUMX ( Employee, VAR StartDate = Employee[Start date] VAR EndDate = Employee[Termination date] VAR NumDays = CALCULATE ( SUM ( 'Date'[Is Working Day] ), KEEPFILTERS ( DATESBETWEEN ( 'Date'[Date], StartDate, EndDate ) ) ) RETURN NumDays )

Re: Measure to calculate number of employed days in period

Anonymous — Fri, 14 Apr 2023 11:56:01 GMT

Thanks so much! Really appreciate it.

topic Measure to calculate number of employed days in period in DAX Commands and Tips

Measure to calculate number of employed days in period

Re: Measure to calculate number of employed days in period

Re: Measure to calculate number of employed days in period