Forum Discussion
Max Formula
Hi I'm trying to work out a measure that let me know how many hours is remaining, but I'm a little stuck
I have two measures:
- Nov-Dec Cap Hours = CALCULATE( IF([Nov. - Dec. Integration Hours]>200, 200, [Nov. - Dec. Integration Hours]),
- Jan-Oct Cap Hours = CALCULATE( IF([Jan-Oct Integration Hours]>200, 200, [Jan-Oct Integration Hours]),
| Person | Nov-Dec | Jan-Oct | Total Hours | Hours Remaining |
| Person A | 113 | 128 | 241 | 109 |
However the problem that I'm getting is that people have more hours remaining then available to use. In the above examplePerson A is capped at 200 hours for Jan-Oct and they already used 128 so they actually only have 91 hours. Is there a way to show that in measure.
Overall they have 350 hours but cannot use more then 200 in each period.
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]
4 Replies
Hi Anonymous ,
Sorry I can't understand what do you mean😣, especially for this "In the above examplePerson A is capped at 200 hours for Jan-Oct and they already used 128 so they actually only have 91 hours." How is this 91 calculated?
If you can, it would be helpful if you could provide some sample data and your expected results, thank you!
Best Regards,
Dino TaoAnonymous 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]
Anonymous
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.
