Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))
Solved! Go to Solution.
Hi moizsherwani,
Try this DAX formula like below:
… return calculate(SUMX(LEAVE,DATEDIFF(TempStartDate,TempEndDate,DAY)), allexcept(LEAVE[EmployeeID]))
Regards,
Jimmy Tao
Hi moizsherwani,
Try this DAX formula like below:
… return calculate(SUMX(LEAVE,DATEDIFF(TempStartDate,TempEndDate,DAY)), allexcept(LEAVE[EmployeeID]))
Regards,
Jimmy Tao
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |