topic Re: Formula Battery With Previous Row and with MIN and MAX in DAX Commands and Tips

Formula Battery With Previous Row and with MIN and MAX

PBINL — Mon, 16 Dec 2024 12:08:37 GMT

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?

Re: Formula Battery With Previous Row and with MIN and MAX

dharmendars007 — Mon, 16 Dec 2024 12:35:16 GMT

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

Re: Formula Battery With Previous Row and with MIN and MAX

PBINL — Mon, 16 Dec 2024 13:33:27 GMT

Hi dharmendars007,

Thankyou for your answer!

But the formula gives an error:

Re: Formula Battery With Previous Row and with MIN and MAX

Anonymous — Tue, 17 Dec 2024 01:47:09 GMT

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.

Re: Formula Battery With Previous Row and with MIN and MAX

PBINL — Tue, 17 Dec 2024 06:57:47 GMT

Hi Anonymous,

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:

Re: Formula Battery With Previous Row and with MIN and MAX

Anonymous — Tue, 17 Dec 2024 07:16:50 GMT

Hi PBINL

Can you tell me what results you expect?

Re: Formula Battery With Previous Row and with MIN and MAX

PBINL — Tue, 17 Dec 2024 08:44:07 GMT

Hi Anonymous,

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.