Formula Battery With Previous Row and with MIN and MAX

[PBINL]

Good afternoon,

I have a question, I am looking for a formula for the contents of a battery. I have an 'in' column that is what comes into the battery and the 'out' column what goes out, in the 'solution' column I have the outcome of what should come from the DAX formula, I do not get the right outcome. It is important that the formula should never fall below 0 and never exceed 100, if the value falls below 0 it must continue to calculate with 0, no more can be extracted from a battery if it is completely empty.

The formula also needs to know what was in the battery in the row before and then continue calculating with that number.

Is there anyone who could help with a correct DAX formula?

[v-nuoc-msft]

Hi @PBINL 

Thank you very much dharmendars007 for your prompt reply.

For your question, here is the method I provided:

“Table”

Create a measure.

Solution = 
VAR CurrentIndex = MAX('Table'[index])
VAR PreviousState = 
    CALCULATE(
        MAX('Table'[Cumulative]),
        FILTER(
            'Table',
            'Table'[index] < CurrentIndex
        )
    )
VAR InValue = MAX('Table'[In])
VAR OutValue = MAX('Table'[Out])
VAR Difference = InValue - OutValue
VAR NewState = COALESCE(PreviousState, 0) + Difference
RETURN 
IF(
    NewState < 0, 
    0,
    IF(
        NewState > 100, 
        100,
        NewState
    )
)

The final result is that if the new cumulative state is less than 0, 0 is returned; if it is greater than 100, 100 is returned; otherwise, the new cumulative state is returned.

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PBINL]

Hi @v-nuoc-msft,

The formule looks good but from row 8 the cumulative isn't working anymore?

I have placed your formula next to my manual 'solution' column:

[v-nuoc-msft]

Hi @PBINL 

Can you tell me what results you expect?

[PBINL]

Hi @v-nuoc-msft,

The outcome of the column 'Solution Nono Chen' should be equal to the 'Solution' column, I made this column manual.
The outcome should be 0 if the battery becomes below zero, but the next row has to calculate from 0. Row 8 is 3 and row 9 is 7 so the battery at row 9 has 10 in it.

[dharmendars007]

Hello @PBINL , 

To handle row-wise calculations where the cumulative sum respects boundaries (0 to 100), use the following pattern

Battery Solution =
VAR CurrentIndex = MAX('Table'[index])
VAR CurrentIn = MAX('Table'[In])
VAR CurrentOut = MAX('Table'[Out])
VAR PreviousBattery =
CALCULATE([Battery Solution],
FILTER('Table', 'Table'[index] = CurrentIndex - 1))
RETURN
MIN(100, MAX(0, PreviousBattery + CurrentIn - CurrentOut))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[PBINL]

Hi @dharmendars007,

Thankyou for your answer!

But the formula gives an error: