Max Formula

[Anonymous]

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:

1. Nov-Dec Cap Hours = CALCULATE( IF([Nov. - Dec. Integration Hours]>200, 200, [Nov. - Dec. Integration Hours]),
2. Jan-Oct Cap Hours = CALCULATE( IF([Jan-Oct Integration Hours]>200, 200, [Jan-Oct Integration Hours]),

I take those two measure to figure out remaining hours which is cap at 350. 

Hours remaining = 350- ([Nov-Dec Cap Hours]+[Jan-Oct Cap Hours])

Example

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. 

[AnalyticsWizard]

@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.

[Anonymous]

@AnalyticsWizard  Thank you, however, I'm getting the original answer of 109

[Anonymous]

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 Tao

[Anonymous · Accepted solution]

@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]