TotalYTD Not Adding Correctly

[AppleMan]

Hi, I am having issues with the totalYTD function in PowerBI (I have also tried to do this with running total and gotten the same result). 

Here is the issue, in the table below I am trying to get the YTD total of the hours worked by salary + hourly employees. The YTD function is doing the hourly portion correctly, but is only including the salary hours of the current month and not the previous months. I have hourly hours, salary hours, total hours (salary + hourly), and am trying to do a YTD of this total hours measure. 

If you look at the table above, the Total Hours YTD for 2-2023 should be 63,974.42 + 56,418.90 = 120,393.32. However, PowerBI is doing 52,537.28 + 46,178.90 + 10,240  = 109,056.18 and excluding the first month of 11,337.14 salary hours.

This trend continues till the last month of the year greatly throwing off the total hours YTD. For some reason the salary hours will not YTD so it only takes the one of month of salary hours data. I have checked and powerBI is able to do time intelligence for salary hours and see what month they fall in, which I assumed was the issue at first. 

Here is an example:
The YTD function will not work for salary hours:

Here is the expected (or wanted) result: 

Here is a link that takes you to a download of a test version of this report I created to demonstrate the issue:

Example PBI Link 

If anyone can show me why this is and how to get the salary hours to correctly total year to date I would greatly appreciate it.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
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

[AppleMan]

I have edited the post to have a link to a test PBI file that also shows the expected results in an image. 

[lbendlin · Accepted solution]

This column definition is iffy:

Salary Hours Column = Calculate(
        ([Active Salaried] * (40/7)) * MAX('Date Table'[Day])
)

as it includes a measure.  Now in this case the measure itself is a constant, but in general the rule is that you cannot create columns from measures/filters.

Here is a proposal for an alternative, more pedestrian calculation. It uses day level granularity rather than relying on ENDOFMONTH.

[AppleMan]

I have one more quick question. How would you alter the [SalaryHrs] measure to not calculate salary hours of months that have not occurred yet. For example, have this table show 0 for month 5-12 of 2023? 

I have some ideas but I have a feeling you will come up with a much more graceful route. 

[AppleMan]

Very interesting solution. That measure is actually not a constant in my real report, I made it one in the example to simplify it. 

I will mark your answer as the solution. I like the way you wrote the YTD function, I will definitely take note of that for my future reports. Thank you!