Difficult Days Off During Pay Period Problem - Direct Query Mode

[moizsherwani]

Hi guys,

I am haaving diificulty covering the last leg of a DAX measure (DirectQuery mode). It is to calculated the days a person has taken off during the pay period, so for example see table below

EMPLOYEE TABLE

EmployeeID - Employee Name

1                  - Adam

2                  - Bob

LEAVE TABLE

EmployeeID - StartDate  - EndDate    - DaysOff

1                  - 1/1/2018   - 1/10/2018 - 9

1                  - 3/1/2018   - 3/2/2018    - 1

1                  - 3/5/2018   - 3/7/2018     - 2

2                  - 3/10/2018 - 3/15/2018   - 5

2                  - 3/25/2018  - 3/26/2018   - 1

NOTE: EndDate is actually the day he will commence work so that day is not counted as a day off

I have a date table (datedim) which is not connected to any table and I will for example set the start and end date to 2/20/2018 to 3/20/2018 (Pay Period). I need as a result the total number of days that a person took off during this period. Summary below

Employee ID - DayOff

1                    - 3 (which is 1 + 2)

2                    - 5 

Please note there is the added complexity that an employee's days off can start from before the Pay Period and end within or even after it as well as starting within the Pay Period and ending after it.

Here is where I have gotten so far and it calculated the days off perfectly fine BUT it doesn't add them up for each employee

DaysOff =
Var MonthFirstDate = FIRSTDATE(datedim[date])
Var MonthEndDate = LASTDATE(datedim[date])
Var LeaveStartDate = min(LEAVE[startdate])
Var LeaveEndDate = min(LEAVE[enddate])
Var TempStartDate = SWITCH(TRUE(),
LeaveStartDate>=MonthFirstDate&&LeaveEndDate<=MonthEndDate,LeaveStartDate,
LeaveStartDate<=MonthFirstDate&&LeaveEndDate>=MonthEndDate,MonthFirstDate,
LeaveStartDate<=MonthFirstDate&&LeaveEndDate<=MonthEndDate&&LeaveEndDate>=MonthFirstDate,MonthFirstDate,
LeaveStartDate>=MonthFirstDate&&LeaveEndDate>=MonthEndDate&&LeaveStartDate<=MonthEndDate,LeaveStartDate,TODAY())
Var TempEndDate = SWITCH(TRUE(),
LeaveStartDate>=MonthFirstDate&&LeaveEndDate<=MonthEndDate,LeaveEndDate,
LeaveStartDate<=MonthFirstDate&&LeaveEndDate>=MonthEndDate,MonthEndDate+1,
LeaveStartDate<=MonthFirstDate&&LeaveEndDate<=MonthEndDate&&LeaveEndDate>=MonthFirstDate,LeaveEndDate,
LeaveStartDate>=MonthFirstDate&&LeaveEndDate>=MonthEndDate&&LeaveStartDate<=MonthEndDate,MonthEndDate+1,TODAY())

return SUMX(LEAVE,DATEDIFF(TempStartDate,TempEndDate,DAY))

[v-yuta-msft · Accepted solution]

Hi moizsherwani,

Try this DAX formula like below:

…

return calculate(SUMX(LEAVE,DATEDIFF(TempStartDate,TempEndDate,DAY)), allexcept(LEAVE[EmployeeID]))

Regards,

Jimmy Tao