Capacity Reporting with Employee Work Hours

[Anonymous]

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

[v-jiascu-msft]

Hi @Anonymous,

Can you mark the proper answer as a solution please?

Best Regards,

Dale

[v-jiascu-msft]

Hi @Anonymous,

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.

Best Regards,

Dale

[Ashish_Mathur]

Hi,

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

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

Hope this helps.