Help me to get the total number of hours employees worked.

[Malsha]

Hi,
I am new to Power BI & DAX. so I need a help to get the total hours of employees worked in weekdays, saturdays & sundays in my dataset.

Final datatable how I need to show in my power bi report should be like this. (as an example)

One employee can work in multiple departments and they can have multiple job roles. they don't have one specific job role. as an example, Jon Bloggs (EMP ID - 8230 as in the above picture) can work as a cleaner in today morning at PR800 department and in the evening can work as a janitor in another department or a same department. 
So I need their total work hours by filtering by the job role and the department.

Here is a picture of my data model.

This is how I need to get the data to above columns.
Employee Name & EMP ID :- Employee Data table
Department :- Subunit table (Subunit ID)
Job Role :- Job Role table (Job Role Name)
Period :- Timesheet table (Timesheet start date / Timesheet end date)

I need to get the total worked hours for those columns by using Actual Duration column in the Timesheet data table.

I use Duration column to identify the week days and holidays. if there's a S in the Duration column, it's a sick leave.  if there's a H in the Duration column, it's a Holiday.  if there's a date in the Duration column, it's a working day. 

can you please help me to get this.

[Anonymous · Accepted solution]

HI @Malsha,

It seems like you want to analyze records based on two date fields, you can take a look at the following blog start, end date part if helps:

Before You Post, Read This: start, end date 
Regards,

Xiaoxin Sheng