Hello,
I'm trying to create a beast mode to calculate gains in values over time to be displayed on a single value summary card. We have both the values and their corresponding dates. We are trying to calculate the gain based on the difference between the lowest and highest value (given that the highest occurs after the lowest) in a date range.
For example:
Date Value
5/20 2.2
5/21 2.1
5/22 2.2
5/23 2.3
5/24 2.7
With this data, we would return a value of 0.6, because the difference between the highest value (2.7) and lowest value (2.1) is 0.6. The highest value also occured after the lowest value.
Because we are taking into account that the highest must occur after the lowest, we cannot do a simple max(value) - min(value) that would omit certain edge cases.
For example, if we filter our dates, here is a case where we do not see a gain over time:
Date Value
5/20 2.2
5/21 2.1
So having our beast mode as max(value) - min(value) would calculate 0.1, and our summary number card would incorrectly show this as a gain when in fact there wasn't, due to the values decreasing over time. We might want to report that there was no gain or -0.1 gain on our summary number card.
Date Value
5/20 2.2
5/21 2.1
5/22 2.2
In this last edge case, we will add another date to our range. Based on our definition, we do see a gain over time of 0.1 where the highest value occurs after our lowest value (from 5/21 to 5/22). So it should correctly display 0.1, but also take into account that this it needs to compare the highest value from the most recent date (5/22 as opposed to 5/21, which was in our last example), so that it can correctly report a gain.
Any hints on how to do this? Trying to sort this out ASAP, and would greatly appreciate the help!
