Forum Discussion
Running total with upper and lower limit
HI, v-easonf-msft ,
E12 stands for the previous cell. What I do here in Excel is building the running total bei adding the previous cell. In E2 is added E1, in E3 is added E2 and so on. This is exactly, where I have difficulties to build it in DAX, especially in combination with the IF-conditions.
I could also add an xlsx-file here, but I'm not sure how that works. Thanks for helping me.
Regards Holger
- v-easonf-msft
Community Support
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 _ EasonI 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!!!
