Forum Discussion
Max Formula
Anonymous Sorry that was a typo it should be 72 hours remaining since, Jan-Oct is capped at 200 hours.
However, I figured it out. I wrote a measure
Jan-Oct hours remaining= 200- [Jan-Oct]. This told me how may hours was left available in the time frame, then I wrote another if formula
Hours cap remaining= iF{[Hours Remaining]>[Jan-Oct Hours Remaining], [Jan-Oct Hours remaining], [Hours remaining]
Solution SupplierAnonymous
Certainly! To adjust your measure and handle the scenario where people have more hours remaining than available, you can modify your existing calculation. Let’s create a new measure that takes into account the capped hours for each period:
Remaining Hours = VAR NovDecCap = MIN(200, [Nov-Dec Cap Hours]) VAR JanOctCap = MIN(200, [Jan-Oct Cap Hours]) VAR TotalCap = 350 VAR UsedHours = NovDecCap + JanOctCap VAR Remaining = TotalCap - UsedHours RETURN IF(Remaining < 0, 0, Remaining)
Explanation:
- We calculate the capped hours for both Nov-Dec and Jan-Oct.
- We sum up the used hours.
- We calculate the remaining hours by subtracting the used hours from the total cap (350).
- If the remaining hours are negative (i.e., more used than available), we display 0; otherwise, we show the actual remaining hours.
Apply this new measure to your matrix, and it should handle the scenario correctly. If you have similar measures, you can follow a similar approach.