Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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!
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!
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!
"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
Date | Employee Name | Activity | Activity Description | No.of Hrs | Type of Entry |
02/08/2021 | Kalam | ADMIN | Admin Work | 2 | Billable |
02/08/2021 | Kalam | TDS | Tds Filing | 2 | Billable |
02/08/2021 | Kalam | AUDIT | Vouching | 3 | Billable |
03/08/2021 | Kalam | Casual Leave | Leave | 4 | Non Billable |
04/08/2021 | Kalam | Study Leave | Leave | 8 | Paid Non Billable |
04/08/2021 | Kalam | Stydy Leave | Leave | 8 | Paid Non Billable |
Employee Name | No.of working Days for the MOnth | Worked Days | Leave (Days) |
Kalam | 27 | 24.5 | 2.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:
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.
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!
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.