I need to calculate a penalty for each row where the penalty is increased for conscutive rows:
The first row with a value above threshold (Index 1, 5, 10) should get the value 1000.
The second consecutive row (Index 2, 6, 11) should get the value 2000.
The third and following rows (Index 3, 7-8, 12) shoud get the value 5000.
I could do a merge to find the preceding row for each row, see if above threshold is true or false and then know if it is the first case but would prefer a nicer way. Is there one?
Please note that the actual data set has data for every hour the last five years, in total about 44000 rows. My experience is that some DAX-examples that work fine on 20 rows never executes on 44000 rows. I would prefer a solution in Power Query.