cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Community Support

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!

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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

9 REPLIES 9
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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

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?

Regular Visitor

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

Community Support

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!

Regular Visitor

Hi

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

Community Support

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!

Regular Visitor
Regular Visitor
Community Support

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!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors