Calculated Column: Return 1st Date where Value in Another Column is Greater Than X
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
Hopefully that makes sense.
I am guessing I need to be using some sort of OVER function, but I am not familiar with how to use them.
Thanks in advance for any tips! :)