Hi Sean! Here's something to get you started:
I used this calculation to get the running total of hours of under- and oversupply. When it's undersupply, it's a negative number; when oversupplied, positive. The absolute value is the number of consecutive values.
IIF(SUM([Imbalance]) < 0, -1, 1) + IIF(SUM([Imbalance])*LOOKUP(SUM([Imbalance]), -1)>0, PREVIOUS_VALUE(0), 0)
Doing WINDOW_MAX() of this calculation gives you the max consecutive oversupply, and doing the WINDOW_MIN() gives you the max undersupply. You can also kind of see it from the color in the graph. Using the tooltips is the best way to interact with this particular viz.
This calculation gives you the running sum integration between the curve and the x-axis, restarting every time it crosses it:
SUM([Imbalance])+IIF(SUM([Imbalance])*LOOKUP(SUM([Imbalance]), -1)>0, PREVIOUS_VALUE(0), 0)
If you'd like to use this in another kind of view, e.g. a table, then it gets a bit more complicated, but we can talk about that if needed.
This is exactly what I was looking for, thanks!
Oh, and I forgot to mention that this is only completely accurate if you have a densely populated data source where every hour is recorded. If there are any gaps in the hours recorded, you could get incorrect consecutive hour calcs. I tried to prevent such issues by using "Show missing values" on the date-time pill, but it's best to just make sure that the data is densely populated.
That makes sense, thank you again.