Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aravinthets
Regular Visitor

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 select activity type as leave (Casual / sick..) then they enter total time.If they take full day leave then they mentioned total hrs as 8 , half day means they mentioned as 3.5 or 4.

  Per day they could do different activity and clock multiple timesheet entries.

 

Solution i need is i want to calculate total leave taken by emloyees. I used following measure to count the row but the problem i am faing is not able to calculate half day leave ,it consider it as full day leave.

 

   I have created one conditional colum to identify that particular row is leave or not.If activity code contains leave then that row is marked as leave else present.

 

 

Leave =
VAR __WeekDayCount =
CALCULATE(
COUNTX(
FILTER('Time sheet','Time sheet'[WeekDayNo] <> 7 && 'Time sheet'[Attendance] = "Leave" )
,'Time sheet'[WeekDayNo] )
)
RETURN __WeekDayCount
2 ACCEPTED SOLUTIONS

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!

View solution in original post

v-kelly-msft
Community Support
Community Support

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:

vkellymsft_0-1630397407507.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

9 REPLIES 9
v-kelly-msft
Community Support
Community Support

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:

vkellymsft_0-1630397407507.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

lbendlin
Super User
Super User

"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?

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

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:

vkellymsft_0-1629773460292.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Hi

   Thanks for your email.I am not able to get the desired result.herewith i have attached the sample data.

 

https://docs.google.com/spreadsheets/d/1F8r8pos-IFMVDiuVbXGbFVXhzvFxU6f1/edit?usp=sharing&ouid=11215...

Hi  @aravinthets ,

 

The link you share returns 401 error,would you pls recheck it?

 

Best Regards,
Kelly

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

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors