Forum Discussion
TotalYTD Not Adding Correctly
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.
I have edited the post to have a link to a test PBI file that also shows the expected results in an image.
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.
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!
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.