Filter another measure so that negative numbers are not returned

[pintobean87]

Hello all,

I am using the following measure that basically rolls up 2 "week ending" periods for each employee, it adds together the "hours" in "week ending" to calculate overtime (measure subtracts 80 from total sum of hours for those two weeks to calculate overtime). The problem I'm having is that when I add the measure to a visual table I'm creating, it displays the total by including both negative and positive numbers. I only want the positive numbers since those are the overtime hours.

So, basically, I'm not sure how to filter out negative numbers so that the total is only made up of positive numbers (overtime).

Measure:

Measure = 
var _a = RANKX(ALLEXCEPT('Table','Table'[Employee]),[Week Ending],MAX('Table'[Week Ending]),ASC,Dense)
var _b = MAXX(FILTER(ALLEXCEPT('Table','Table'[Employee]),[Week Ending]<MAX('Table'[Week Ending])),[Week Ending])
var _c = CALCULATE(SUM('Table'[Hours]),FILTER(ALL('Table'),[Employee]=MAX('Table'[Employee])&&[Week Ending]<=MAX('Table'[Week Ending])&&[Week Ending]>=_b))-80
return IF(_a=ODD(_a),BLANK(),_c)

[v-jianboli-msft · Accepted solution]

Hi @pintobean87 ,

Here is the sample data:

Please try:

Measure = 
var _a = RANKX(ALLEXCEPT('Table','Table'[Employee]),[Week Ending],MAX('Table'[Week Ending]),ASC,Dense)
var _b = MAXX(FILTER(ALLEXCEPT('Table','Table'[Employee]),[Week Ending]<MAX('Table'[Week Ending])),[Week Ending])
var _c = CALCULATE(SUM('Table'[Hours]),FILTER(ALL('Table'),[Employee]=MAX('Table'[Employee])&&[Week Ending]<=MAX('Table'[Week Ending])&&[Week Ending]>=_b))-80
return IF(_a=ODD(_a)||_c<0,BLANK(),_c)

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.