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...
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    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.

     

lbendlin's avatar
lbendlin
Super User
3 years ago

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's avatar
AppleMan
Helper III
3 years ago

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.