Forum Discussion
Helper IRecursive DAX Calculated Column
The most efficient solution would be to use a measure instead of a calculated column.
I'd solve this by creating two measures:
Total Over Capacity = SUM(YourData[HoursOverCapacity]) - SUM(YourData[HoursUnderCapacity])Over Capacity To Date = var filterDate = LASTDATE(YourData[Date])
var output = CALCULATE(
[Total Over Capacity],
ALL(YourData),
YourData[Date] <= filterDate
)
RETURN
output
Helper IAnonymous
Okay, so that's close to what I'm looking for, but when the total hours over capacity is negative, or rather, there's hours under capacity, I don't want it to add those hours to the total, just the hours over capacity. I want the hours under capacity to be capped at a different variable, called HoursPlanned.
Here's the new logic of something that I'd like:
TotalHOC =
IF (
(SUM('Testing Totals'[HoursOverCapacity]) - SUM('Testing Totals'[HoursUnderCapacity])) > 0,
SUM('Testing Totals'[HoursOverCapacity]) - SUM('Testing Totals'[HoursUnderCapacity]),
IF (
ABS(SUM('Testing Totals'[HoursOverCapacity]) - SUM('Testing Totals'[HoursUnderCapacity])) > SUM('Testing Totals'[HoursPlanned]),
SUM('Testing Totals'[HoursPlanned]),
ABS(SUM('Testing Totals'[HoursOverCapacity]) - SUM('Testing Totals'[HoursUnderCapacity]))
)
)
So, for an example with this picture; the hours under capacity is capped at 18.75, the constant of HoursPlanned, but I'd like the hours over capacity to be added/subtracted from the hours under capacity. I guess it would be better to have another measure?
- amitchandak
Super User
Can you share sample data and sample output like the one before with planned hours too
- Lyssillic
Yeah, here amitchandak :
The HoursOverCapacity is when the HoursRemaining - HoursPlanned is positive.
The HoursUnderCapacity is when the HoursRemaining - HoursPlanned is negative (rather, when HoursPlanned - HoursRemaining is positive).
Thus, the HoursUnderCapacity can never be larger than the HoursPlanned, because you can't have more free hours of the day than what was planned for that day. (HoursUnderCapacity is capped at HoursPlanned)
Date HoursPlanned HoursRemaining HoursUnderCapacity HoursOverCapacity 3/8/20 16 20 0 4 3/9/20 16 26 0 10 3/10/20 16 5 11 0 3/11/20 16 2 14 0 3/12/20 16 20 0 4 Here's the output I'm looking for:
The 4 hours over capacity on 3/8/20 is added to the hours remaining of 3/9/20, equaling 30, which 30 - 16 = 14 for the HoursOverCapacity for that day. Then the 14 hours over capacity from 3/9/20 gets added to the hours remaining of 3/10/20, equaling 19. 19 - 16 = 3, which is the hours over capacity for 3/10/20. Then the 3 hours over capacity is added to hours remaining of 3/11/20, equaling 5. 5 - 16 = -11, which is negative, so 11 goes to 3/11/20's HoursUnderCapacity. The HoursUnderCapacity, however, doesn't go to the next day, only the HoursOverCapacity because it's carrying over the work that still needs to be done. The free hours of the day don't carry over to the next day, so 3/12/20 still has 4 hours over capacity.
Date HoursPlanned HoursRemaining HoursUnderCapacity HoursOverCapacity 3/8/20 16 20 0 4 3/9/20 16 30 0 14 3/10/20 16 19 0 3 3/11/20 16 5 11 0 3/12/20 16 20 0 4 - amitchandak
Please find a solution assuming you need to calculate it based on hour planned and hour remaining. Find attached file.
