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!

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!