Find YTD % when each month has a set number of hours that is not derived from data table actual hrs

[95Bigbluetoy]

I am trying to calculate the YTD % Overhead hrs for each employee. Sounds simple enough and would be if based on the data in my table.  My boss wants this based on 173.3 hrs per month (40 hrs wk x 52 wks divided by 12) and not actual total hrs for each employee. I have my %OH hrs column which takes the users sum of OH hrs (based on certain cost center columns) and divides that by 173.3 x 100. This gives me the %OH hrs for any one month period. I can get each users YTD total OH hrs, but cannot figure out how to divide that by the number of months x 173.3, for example: user A has a YTD of 255 OH hrs through August (8th month). I need to have 255/(8x173.3) which would equal 18.39%. i need to be able to calculate the YTD %OH hrs for any number of months.

any and all help is greatly appreciated.

[PANDAmonium]

So I might be misunderstanding but hopefully this helps...

You have the number of hours, 255. And you have a set hours per month, 173.3 . And you have the month as text. So to get [Hours]/([Month]*[Hours per Month]) all your missing is the month as a numeric value.

I haven't tested these, but your forumla for YTD % OH should be something like the following. The first of each depending on where your creating the column is for if you're looking at historical data. The second is if you having a running total.

DAX:

[Hours]/(Month([Date])*[Hours per Month]

or [Hours]/MONTH(NOW())*[Hours per Month])

M:

[Hours]/(Date.Month([Column1])*[Hours per Month]

or [Hours]/(Date.Month(Date.From(DateTime.LocalNow()))*[Hours per Month]

[95Bigbluetoy]

Sorry for the delay in response, but had to go out of town for a funeral.

I dont have the Month as text. In my table that is pulling from SQL, i have a column titled "Period_End_Date" which is data type: Date/Time, which i changed the format to just date (mm/dd/yy).  This column shows every pay week ending date. The 173.3 total hrs is not derived from the tables but is just the average of hrs per month based on 40 hrs week x 52 weeks diveded by 12 months. 

i am looking for a running total (YTD) for each employee's OH hrs and %OH hrs. I can easily get the total OH hrs as that is just selecting all months in the filter. Getting the YTD %OH hrs is where i am having the issue. Based on what you are saying, it appears that i need to create a column/measure for Month that would have the numeric value of the month so as to then mulitply it by 173.3. 

Basically using your formulas, i would replace [Hours per month] with 173.3, once i have the month in numeric value....correct?

[PANDAmonium · Accepted solution]

Okay, included some screenshots. So if your data is formatted similar to the table on the left, you can add a new measure using:

YTD % OH = CALCULATE(SUM([OH Hours]),YEAR('OH Hours'[Period_End_Date]) = YEAR(NOW()))/(MONTH(NOW())*173.3)

This will caculate total OH Hours for the current year divided by the numeric value for the current month times your avg monthly hours. Included screenshots of what the results look like and you can validate the percentages by taking the OH Hours / (9 * 173.3)

[95Bigbluetoy]

Yes! That is exactly what my data table looks like. Thank you so much for your help. New to Power Bi and DAX. Your formula worked great. Thanks again for your help.

[PANDAmonium]

No actually... I misunderstood some stuff and didn't full think out the formula since I was in a rush to leave, so give me a couple minutes and I'll rewrite the formula and include some screenshots.