Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

  • 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

    • Anonymous's avatar
      Anonymous
      Not applicable

      TomMartens 

      Thanks a lot, that was exectly waht i was looking for !!! 

       

      Thanks !