Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Working Days for employees within the selected range

I want to calculate the no. of working days within a selected range through slicer but there are three cases that needs to be kept in mind while calculating the working days:

Lets say start date is 01/01/2019 and finish date is 31/01/2019.

  1. For normal case the start date and finish date is same (Days:31)
  2. For new joinee case whose joining date is 10/01/2019 so start date will become 10/01/2019 and finish date is same (Days:21)
  3. For resigned employee case whose last working day is 15/01/2019 so start date is same and finish date will become 15/01/2019 (Days:16)
  4. If all are selected (Days: 31+21+16 = 68)
Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      As per your solution you are using MAX but because of this if more than 1 items are selected then it takes the max value from the column. I want if employee A has 15 days and employee B has 25 days then if both are selected then result should be 40 days but using the MAX the result is coming to be as 50 days.

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    You can create measure like DAX below.

     

     

    CountDay=
VAR MaxDate=CALCULATE(MAX( Table1[Date]), ALLSELECTED( Table1[Date]))
VAR EmployeeDate=CALCULATE (FIRSTNONBLANK ( Table1[Date], 1 ),FILTER ( ALLSELECTED( Table1), Table1[Employee Type] = MAX(Table1[Employee Type]) ))   
VAR _table= SUMMARIZE(Table1,Table1[Employee Type] , "DateDiff", DATEDIFF(EmployeeDate, MaxDate, DAY) )
RETURN 
SUMX ( _table, [_Value] )

     

     

    If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-xicai   I have two tables one is calender table and another one is the table1 having the data for the joining dates and last working date. So i have crated a all the measures in the calender table and created max date and min date and then doing datediff but when all is selected the answer is coming wrong. The measures are as follows, I am checking that if the entry is in the LastWorkingDay or DateofJoining then take that date otherwise take calendar date against the employee name. I am getting the correct values if indivdual employee is selected but if multiple or all is selected result comes wrong. Greg_Deckler 

      MinDate =
      IF(AND(MIN(Table1[DateofJoining]) >= MIN('Calendar'[Date]), MIN(Table1[DateofJoining]) <= MAX('Calendar'[Date])), MIN(Table1[DateofJoining]) , MIN('Calendar'[Date]))
       
      MaxDate =
      IF(AND(MIN('Calendar'[Date]) <= MAX(Table1[LastWorkingDay]), MAX('Calendar'[Date]) >= MAX(Table1[LastWorkingDay])), MAX(Table[LastWorkingDay]), MAX('Calendar'[Date]))
       
      Days =
      DATEDIFF ( [MinDate],[MaxDate], DAY )+1