Running total with upper and lower limit

[Mauerwinkel]

Hello,

I want to calculate a running total with upper and lower limit. In my example upper limit is 8000, lower is 0. The formula in excel looks like this:

=WENN(([@[Einspeisung (Wh)]]-[@[Bezug (Wh)]]+E12) < 0; 0; WENN(([@[Einspeisung (Wh)]]-[@[Bezug (Wh)]]+E12) > $G$2; $G$2; [@[Einspeisung (Wh)]]-[@[Bezug (Wh)]]+E12))      (WENN means IF)

The corresponding table:

How would this formula look in DAX? Thanks for your proposals.

Holger

[Mauerwinkel]

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]

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

[BlueAnalytics]

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!!!

[v-easonf-msft]

Hi， @Mauerwinkel 

Not fully sure what you want. Can you share the expected result in excel?

Does the value in the cell E12 in the formula have any special meaning?

Please check if the following formula helps:

Result =
VAR E12 = 81
VAR a = 'Table'[Einspeisung (Wh)] - 'Table'[Bezug (Wh)] + E12
RETURN
    IF ( a < 0, 0, IF ( a > 8000, 8000 ) )

Best Regards,
Community Support Team _ Eason