Fitst Negative number before the first positive number

[OscarSuarez10]

Hello How can I get the first negative number  for each ID and Task before the first positive number like in the following table?

ID: 0 TASK: 1 First negative number before first positive number = -20

ID: 11 TASK: 3  First negative number before first positive number = -10

ID: 5 TASK: 2  First negative number before first positive number = -8

ID	TASK	PROD
0	1	-100
0	1	-50
0	1	-20
0	1	10
0	1	40
0	1	100
11	3	-50
11	3	-10
11	3	20
11	3	-2
11	3	-10
11	3	10
5	2	-50
5	2	-8
5	2	20
5	2	-5
5	2	30

[Nathaniel_C · Accepted solution]

Hi @OscarSuarez10 ,

You will need to go to Power Query and add a index column.

Then

Get pos =
VAR _getfirstpos =
    CALCULATE (
        MIN ( Neg[Index] ),
        FILTER ( ALLEXCEPT ( Neg, Neg[ID], Neg[TASK] ), Neg[PROD] > 0 )
    )
VAR _getfirstneg =
    CALCULATE (
        MAX ( Neg[PROD] ),
        FILTER ( ALLEXCEPT ( Neg, Neg[ID], Neg[TASK] ), Neg[Index] < _getfirstpos )
    )
RETURN
    _getfirstneg

to get this 

 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C]

Hi @OscarSuarez10 ,

When you say the  first negative number, are you looking for the highest negative number?

Nathaniel

[OscarSuarez10]

Hi @Nathaniel_C  no, I am looking for the first negative number that appears immediatly before the first positive number according to the Product column

[Nathaniel_C]

Ok, I figured that.  How does that solution work for you? Nathaniel