cancel
Showing results for
Did you mean:  Helper V

## Insert a limit in a measure to avoid below 0 or above 100

I have a 3 measures related to absence:

1 Calculate number of working days in month - (2.5 days leave allowed)

Measure 5.1 =

VAR _Month = CALCULATE(COUNT(DimDate[Days in Month]),FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK()))

VAR _lastday=
COUNTROWS(
FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK()))-2.5

RETURN
IF ( ISBLANK ( [AbsentByDay] ), BLANK (), _lastday )

2. Number of absence days from dates : [AbsentbyDay]

3. Measure 1 and Measure 2 subtracted
Measure 5.5 =

VAR _Month = CALCULATE(COUNT(DimDate[Days in Month]),FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK()))

VAR _lastday=
COUNTROWS(
FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK()))-2.5

RETURN
IF ( ISBLANK ( [AbsentByDay] ), BLANK (), _lastday )-[AbsentByDay] My question is : is there a way to limit the NET values in table 3 from going below 0 / over 100

Thanks in advance, apologies if I haven't explained this properly.

File attached to help

https://www.dropbox.com/s/u5ztxrg4g2g5qpi/Sample3%28updated%29.pbix?dl=0 How about wrapping the result inside a MAX

if it is negative, you get zero instead

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.  