Model battery charge

[Matthias93]

Editing in order to give it a bump, but as already quite some people have viewed the question without answering I'm starting to wonder if this is possible. Is Power BI suited for this type of calculations? In Excel this would be quite easy.

Hi, I want to model the energy stored in a battery that is connected to solar panel.

If solar power > load, I can charge the battery up to its maximum capacity.
If solar power < load, I can draw energy from the battery down to 0.

I have a measure called excess  that calculates solar power - load (in 15 minute intervals)
Edit: I could make excess a calculated column if that helps.

I now want to create a new measure that calculates for each interval the total energy stored in the battery.
Edit: a calculated column would do as well.

Assume max capacity battery = 1MWh

Time	Excess (MW)	Energy stored (MWh)
0:00	0,5	0,125		0,25 * 0,5 as it is the energy delivered in 15 minutes
0:15	1	0,375		0,25 * 1 + 0,125
0:30	1	0,625		0,25 * 1 + 0,375
0:45	2	1		Topped at 1 as 0,25 * 2 + 0,625 > 1
1:00	2	1		Topped at 1
1:15	-1	0,75		1 - 0,25*1 starting to draw energy
1:30	-2	0,25		0,75 - 0,25*2
1:45	-2	0		Cut at 0 as 0,25 - 2*0,25 < 0
				etc…

 I am quite new at BI, so any help would be appreciated.

[Greg_Deckler]

First, where does the .25 come from? Second, I am skeptical that you are going to solve that in DAX. I might play around to confirm, but what I see there essentially requires recursion or looping. DAX really doesn't like recursion. So, you are going to have to solve that in Power Query "M" code. See my series on "Fun with Graphing in Power BI" starting with the 3rd part (Part 3i). You will probably at least need the technique described in the Sierpinski Triangle article:

https://www.linkedin.com/pulse/fun-graphing-power-bi-part-3i-greg-deckler-microsoft-mvp-/