Sum of Column in Table 1 divided by Count of Column in Table 2 with Dates

[sthompson]

Hi guys, I'm a budding PowerBI user and am very stuck. I am building a dashboard to track our headcount across the various clients we support and I have a table that has a variety of columns, but the main ones relevant here are Check Date, Hours, Employee, Client. Also of note, it does have Code, which is the hours code - so the raw data has multiple rows for the same employee for a given paycheck with the different time codes they entered. I've set up a few visuals that summarize this information well. This information is coming from our payroll data and check dates are every 2 weeks, although if someone is terminated there is an off-cycle check issued for them so there is some extra data that breaks this rule. 

What I'm trying to do is set up an FTE calculation (Full-time equivalent). Basically, Hours / (Pay Periods * 80). So if there was 320 hours / (4 pay periods * 80), it would yield 1 FTE. I have a separate table that just lists out the dates of past and future paycheck dates. (9/23, 9/30, 10/14, etc.). Basically, most months have a total available working hours for a single full-time person of (2 paychecks * 160 hours/paycheck = 320 hours), however, typically June/December have 3 pay periods due to how the every-other-week schedule lines up with the weekend dates, so those months have (3 paychecks * 160 hours/paycheck) = 480 available hours). So I need some way to adjust for the number of paychecks scheduled in a given month, rather than just divide by a flat number.  For simplicity, I'll call the table with hours tHours and the table with paycheck dates tPayDates. 

I'm trying to essentially create a measure that divides Sum( tHours[Hours]) by Count(tPayPeriods[Date}) * 80. Seems simple enough, but I've been stumped at every turn. One key need is for the date hierarchy to remain in the tHours table, so we can summarize at the weekly (paycheck) level as well as zoom out to Month/Quarter/Year. At one point, I drew a relationship between the dates on the two tables, but that removed the date hierarchy. If I don't do the relationship, it seems like it's kind of taking the the Pay Periods count and putting it on every line. 

Sample Hours table below. Basically, if I were viewing the data at a monthly level, I would want to sum the August hours, and divide by 2 (which the pay dates table would have two entires in August - 8/12/22 and 8/26/22). I cannot figure this out and spent most of yesterday on it. Any help is greatly appreciated. 

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523