471views

Calculated Column: Return 1st Date where Value in Another Column is Greater Than X

#over

Hi,

I was hoping to get some help with the correct equation for a calculated column.

My current dataset looks something like this:

Date EntityName Dataset Value

1/1/2018 Apple #1 0

2/1/2018 Apple #1 0

3/1/2018 Apple #1 50

4/1/2018 Apple #1 100

5/1/2018 Apple #1 0

6/1/2018 Apple #1 50

7/1/2018 Apple #1 200

8/1/2018 Apple #1 150

1/1/2018 Apple #2 100

2/1/2018 Apple #2 200

3/1/2018 Apple #2 75

4/1/2018 Apple #2 100

5/1/2018 Apple #2 0

6/1/2018 Apple #2 50

7/1/2018 Apple #2 200

8/1/2018 Apple #2 150

1/1/2018 Pear #2 3

2/1/2018 Pear #2 200

3/1/2018 Pear #2 75

I would like a new calculated column that can tell me what the Date was where the first Value exceeded a threshold (for example 10), for each Entity/Dataset group.

For example, it would look like this for the above dataset:

Date EntityName Dataset Value FirstDate>10

1/1/2018 Apple #1 0 3/1/2018

2/1/2018 Apple #1 0 3/1/2018

3/1/2018 Apple #1 50 3/1/2018 * This date is the 1st occurrence of >10 for Apple #1

4/1/2018 Apple #1 100 3/1/2018

5/1/2018 Apple #1 0 3/1/2018

6/1/2018 Apple #1 50     3/1/2018

7/1/2018 Apple #1 200    3/1/2018

8/1/2018 Apple #1 150 3/1/2018

1/1/2018 Apple #2 100 1/1/2018 * This date is the 1st occurrence of >10 for Apple #2

2/1/2018 Apple #2 200    1/1/2018

3/1/2018 Apple #2 75    1/1/2018

4/1/2018 Apple #2 100 1/1/2018

5/1/2018 Apple #2 0 1/1/2018

6/1/2018 Apple #2 50 1/1/2018

7/1/2018 Apple #2 200 1/1/2018

8/1/2018 Apple #2 150 1/1/2018

1/1/2018 Pear   #2 3 2/1/2018

2/1/2018 Pear #2 200 2/1/2018 * This date is the 1st occurrence of > 10 for Pear #2

3/1/2018 Pear    #2 75    2/1/2018