cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Capacity Reporting with Employee Work Hours

I want to show in a visual that an employee worked a certain amount of hours on a given date; and also show if it was above or under capacity for the day.

  • I want this visual to be used for the week & month, too.

 

I also am trying to show which employees are working over time and at what rate.

 

Here is a sample of my data

 

Date    |  Employee | Work Hours | Capacity by Day | Capacity by Week | Capacity by Month

 

1/1/18    John S       6                      8                           40                           160

1/3/18    Mike M     13                    8                           40                           160

2/3/18    Susie R      6                     8                            40                           160

3/2/18   John S        8.5                   8                           40                            160

3 REPLIES 3
Super User
Super User

Re: Capacity Reporting with Employee Work Hours

Hi,

 

In a calculated column, you can write a simple Overtime formula

 

=MAX([Work hours]-[Capacity by day],0)

 

Hope this helps.

Community Support Team
Community Support Team

Re: Capacity Reporting with Employee Work Hours

Hi @ThePowerBIQueen,

 

Please check out the demo in the attachment.

I would suggest you make some changes.

1. Create a date table like below.

DateTable =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2018, 12, 31 ) ),
    "Week", WEEKNUM ( [Date], 2 ),
    "Month", MONTH ( [Date] ),
    "CapacityByDay", IF ( WEEKDAY ( [Date], 2 ) IN { 1, 2, 3, 4, 5 }, 8, 0 )
)

2. Create a measure. 

Measure = sum('Table1'[Work Hours]) - sum(DateTable[CapacityByDay])

3. Create a Matrix visual. You can drill down to see different results.

Capacity_Reporting_with_Employee_Work_Hours

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Capacity Reporting with Employee Work Hours

Hi @ThePowerBIQueen,

 

Can you mark the proper answer as a solution please?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.