Forum Discussion

AppleMan's avatar
AppleMan
Helper III
3 years ago
Solved

TotalYTD Not Adding Correctly

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.

 

 

 

  • 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.

     

    ExampleHours.pbix207 KB

5 Replies