Forum Discussion
Measure : Calculate working days
Hi all,
I am trying to create a measure to calculate the number of working days between 2 dates for each month and when I'll display the data on the matrix it returns sum od the rows:
So I have created 2 measure to try to get the results that I was expecting:
Emp Worked Days 1 =
VAR EmpHiringDate = SELECTEDVALUE(PAYROLL[Most Recent Hire Date])
VAR EmpLeaveDate = SELECTEDVALUE(PAYROLL[Administrative End Date])
VAR MonthLastDAY = LASTDATE('CALENDAR'[Date])
VAR MonthFirstDAY = FIRSTDATE('CALENDAR'[Date])
VAR HiringWorkDay = IF(NOT(ISBLANK(EmpHiringDate)),CALCULATE(SUM('CALENDAR'[W Day]),DATESBETWEEN('CALENDAR'[Date], EmpHiringDate, MonthLastDAY)),0)
VAR LeaveWorkDay = IF(ISBLANK(EmpLeaveDate),0,IF(EmpLeaveDate>=MonthFirstDAY, CALCULATE(SUM('CALENDAR'[W Day]),DATESBETWEEN('CALENDAR'[Date], MonthFirstDAY, EmpLeaveDate)),CALCULATE(SUM('CALENDAR'[W Day]),DATESBETWEEN('CALENDAR'[Date], EmpHiringDate, EmpLeaveDate))))
VAR EmpWksDay = IF(
MONTH(EmpLeaveDate)=SELECTEDVALUE('CALENDAR'[Month number]) && YEAR(EmpLeaveDate)=SELECTEDVALUE('CALENDAR'[Year]),
LeaveWorkDay,
IF(HiringWorkDay>=[Working days],[Working days],HiringWorkDay))
RETURN
EmpWksDay
emp Worked Days 2 = IF(NOT(ISBLANK(MAX(PAYROLL[STARTDATE]))),
CALCULATE(SUM('CALENDAR'[W Day]),
DATESBETWEEN('CALENDAR'[Date],MAX(PAYROLL[STARTDATE]),MAX(PAYROLL[ENDDATE]))))
However, both measures are returning the right figures for each row, but the total is wrong, for the example it should be 44 as total:
So I have create one more measure to try to get it sorted, but it shows the same figures as the measure Emp woked days 2 :
TOTAL Woked days = IF(HASONEFILTER('CALENDAR'[Month]),SUMX('CALENDAR',[emp Worked Days 2]))
Please, is there anyway to display the figures for total correct or another way of calculating the working days between 2 dates for each month following the criterias:
1. IF most recent hire date < Startofmonth('Calendar' [date]) = most recent hire date ;
2. IF most recent hire date > Startofmonth('Calendar' [date]) = Startofmonth('Calendar' [date]) ;
3. IF Administrative end date > Endofmonth('Calendar' [date]) =Endofmonth('Calendar' [date]) ;
4. IF Administrative end date < Endofmonth('Calendar' [date]) =Administrative end date ;
It's getting me nuts. :manfrustrated:
here is the pbix that i am using to make the trial.
Hey,
I rewrote your measure a little:
_emp Worked days2 = SUMX( VALUES('PAYROLL'[Payroll nb]) ,[emp Worked Days 2] )SUMX is one of the table iterator functions, that iterates over all the rows in the 1st parameter. In the total line there are 3 rows. Here is a little screenshot based on your pbix file with the sample data:
Hopefully, this provides what you are looking for.
Regards,
Tom
2 Replies
- TomMartensSuper User
Hey,
I rewrote your measure a little:
_emp Worked days2 = SUMX( VALUES('PAYROLL'[Payroll nb]) ,[emp Worked Days 2] )SUMX is one of the table iterator functions, that iterates over all the rows in the 1st parameter. In the total line there are 3 rows. Here is a little screenshot based on your pbix file with the sample data:
Hopefully, this provides what you are looking for.
Regards,
Tom
- AnonymousNot applicable