Forum Discussion
Running total with upper and lower limit
Hi, Mauerwinkel
This kind of recursive is easy to implement in excel, but Dax is not good at handling it.
Best Regards,
Community Support Team _ Eason
I do have exactly the same issue on my desk for some months and I am unable to solve it in DAX so far. I will try to reexplain the issue and any help is highly appreciated:
The issue is about a battery storage for a wind power plant. We sell the energy to traders, but right now we must accept discounts, as we are not able to guarantee a predicted energy amount 24 hours in advance. There is always a difference, let's call it [delta], between the forecast and the real production. We intend to use batteries to reduce this difference. My task is to calculate the optimal size of these batteries. To do so, I must split the delta to the amount, which can be stored in the battery and the residual delta, for which we will have to pay a penalty.
Example:
Let's asume the battery has a capacity of 5 and an initial state of charge [Battery_SOC] of 2,5, reflecting 50% charging. The following table shows the [Delta] between production and forecast. As long as the [Battery_SOC] is in the interval [0..5] the [Delta] is assigned to [Delta_Battery]. Any residual [Delta] is assigned to [Delta_Grid]. In consequence we get the following charging figures:
| TimeStamp BOP | TimeStamp EOP | Delta | Battery_SOC_BOP | Battery_SOC_EOP | Delta_Battery | Delta_Grid |
| 0:00 | 0:15 | 0,9 | 0,5 | 1,4 | 1,4 | 0,0 |
| 0:15 | 0:30 | 1,1 | 1,4 | 2,6 | 1,1 | 0,0 |
| 0:30 | 0:45 | 0,8 | 2,6 | 3,4 | 0,8 | 0,0 |
| 0:45 | 1:00 | 1,5 | 3,4 | 4,9 | 1,5 | 0,0 |
| 1:00 | 1:15 | 0,7 | 4,9 | 5,0 | 0,1 | 0,6 |
| 1:15 | 1:30 | 0,7 | 5,0 | 5,0 | 0,0 | 0,7 |
| 1:30 | 1:45 | -2,0 | 5,0 | 3,0 | -2,0 | 0,0 |
| 1:45 | 2:00 | -1,5 | 3,0 | 1,5 | -1,5 | 0,0 |
| 2:00 | 2:15 | -4,0 | 1,5 | 0,0 | -1,5 | -2,5 |
| 2:15 | 2:30 | 1,0 | 0,0 | 1,0 | 1,0 | 0,0 |
The calculation is easy in Excel, but given the extreme high numer of datapoints, the calculation must be performed in PowerBI (DAX or M). I am simply unable get the right code for Battery_SOC_BOP, which is always Battery_SOC_EOP from the periode before.
Thank you in advance for your support!!!