Capacity Reporting with Employee Work Hours

Question

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 &amp; month, too.&nbsp;I also am trying to show which employees are working over time and at what rate.&nbsp;Here is a sample of my data&nbsp;Date&nbsp; &nbsp; |&nbsp; Employee | Work Hours | Capacity by Day | Capacity by Week | Capacity by Month&nbsp;1/1/18&nbsp; &nbsp; John S&nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;40&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1601/3/18&nbsp; &nbsp; Mike M&nbsp; &nbsp; &nbsp;13&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;40&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1602/3/18&nbsp; &nbsp; Susie R&nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 40&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1603/2/18&nbsp; &nbsp;John S&nbsp; &nbsp; &nbsp; &nbsp; 8.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;40&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 160

ashish_mathur · Answer

Hi,&nbsp;In a calculated column, you can write a simple Overtime formula&nbsp;=MAX([Work hours]-[Capacity by day],0)&nbsp;Hope this helps.

v-jiascu-msft · Answer

Hi&nbsp;Anonymous,
&nbsp;
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.&nbsp;
Measure = sum('Table1'[Work Hours]) - sum(DateTable[CapacityByDay])
3. Create a Matrix visual. You can drill down to see different results.

&nbsp;
&nbsp;
Best Regards,
Dale

v-jiascu-msft · Answer

Hi&nbsp;Anonymous,
&nbsp;
Can you mark the proper answer as a solution please?
&nbsp;
Best Regards,
Dale

Forum Discussion

Capacity Reporting with Employee Work Hours

3 Replies

Related content

DAX for Financial Year Running Report Employee

Calculate Employee Capacity

Yearly Running Report Employees within Organisation

Power BI Report Server Pricing vs Fabric Capacity

Missing employee