Forum Discussion

aravinthets's avatar
aravinthets
Regular Visitor
4 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!

aravinthets's avatar
aravinthets
Regular Visitor
4 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!