Available Hours in Calendar Month

[gkllc]

I 'thought' this would be easy but thus far I'm stymied. I have employees who work varying schedules. I know their total hours by day of the week that I need to calculate into monthly 'hours available' by month/by employee to then calculate how full their schedules are based on appointment visits.
For example
Sue works 32 hours per week that breaks out this way:
M - 10
T - 8
W - 2
Th -5
F - 7

John works 40 hours a week that breaks out
M - 9
T - 8
W- 9
Th - 6
Fr - 8

I'm trying to figure out what DAX formula would calculate total available hours calendar month hours

IE in Oct 2020 there were 4 Mondays, 4 Tuesdays, 4 Wednesdays, 5 Thursdays & 5 Fridays
Thus Sue would have 135 hours available and John would have 174 available hours....

Seems like this should be an easy 'thing' but thus I've not figured out how to get DAX to do what I'm asking.

Thanks in Advance

 

[Matt Allington]

It’s relatively complex. How is your data for staff rosters stored? If I assume you have this data in a table like you imply (I will call it roster) you could create a many to many relationship between this roster table day of week to the day name in the calendar table (single filter direction from calendar to roster)

then you could write this. I think it will work - I haven’t tested it.

=SUMX(values(Calendar[day name]),CALCULATE(sum(roster[daily hours]) * countrows(calendar)))

put the months and years into rows, and the above measure into values.

 

[gkllc]

Yes employee data is in a table - name, total week hours, mon, tue, wed, thu, fri (each day total hours)

This looks like an interesting way to attack it. Hadn't thought about adding a calendar table but will do that and see if the math works. Will report back. Thanks for the idea...and letting me know it's not as simple as I thought it should be.