Forum Discussion

aravinthets's avatar
aravinthets
Regular Visitor
5 years ago
Solved

Need Help on DAX

Hi      I have a data set of employee time sheet which contains the following data. 1.Date 2.Employee Name 3.Hrs worked 4.Activity          if employee want to add leave then he / she should...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  aravinthets ,

     

    Still unauthorized.Would you pls modify the link as public and reshare again?

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  aravinthets ,

     

    Sorry for the late reply.

    First create a calculated column to check whether that day is a working day.

    Is workingday = IF(WEEKDAY('Sheet1'[Date],2)=7,0,1)

    Then create a  measure as below:

    Total leave =
DIVIDE (
    CALCULATE (
        SUM ( 'Sheet1'[total_hours] ),
        FILTER (
            ALL ( Sheet1 ),
            'Sheet1'[fullname] = MAX ( 'Sheet1'[fullname] )
                && 'Sheet1'[Is workingday] = 1
        )
    ),
    24
)

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my areply as a solution!

lbendlin's avatar
lbendlin
Super User
5 years ago

"Solution i need is i want to calculate total leave taken by emloyees"

 

In what unit of measure?  "Number of days when they took some form of leave" or "Total number of hours taken leave"?  What will you do next with this number?

aravinthets's avatar
aravinthets
Regular Visitor
5 years ago

I will give some example of data set and what i want

DateEmployee NameActivityActivity DescriptionNo.of HrsType of Entry
02/08/2021KalamADMINAdmin Work2Billable
02/08/2021KalamTDSTds Filing2Billable
02/08/2021KalamAUDITVouching3Billable
03/08/2021KalamCasual LeaveLeave4Non Billable
04/08/2021KalamStudy LeaveLeave8Paid Non Billable
04/08/2021KalamStydy LeaveLeave8Paid Non Billable
      

 

 

Employee NameNo.of working Days for the MOnthWorked DaysLeave (Days)
Kalam2724.52.5

   

Leave Days Calculation = 8 Hrs = Full Day Leave , 3 or 4 hrs = Half Day Leave

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    4 years ago

    Hi  aravinthets ,

     

    Create 2 measures as below:

    Leave (Days) =
VAR _halfday =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Employee Name] = MAX ( 'Table'[Employee Name] )
                && 'Table'[Activity Description] = "Leave"
                && 'Table'[No.of Hrs] IN { 3.5, 4 }
        )
    )
VAR _wholeday =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Employee Name] = MAX ( 'Table'[Employee Name] )
                && 'Table'[Activity Description] = "Leave"
                && 'Table'[No.of Hrs] = 8
        )
    )
RETURN
    _wholeday + _halfday * 0.5

    Worked Days =
CALCULATE (
    SUM ( 'Table'[No.of Hrs] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Employee Name] = MAX ( 'Table'[Employee Name] )
    )
) - 'Table'[Leave (Days)]

    And you will see：

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!