Use interpolated data as source for daily average

Hi!
I am currently fiddling with Grafana. Part of it is finding out whether the things I have in mind eventually make sense altogether :slight_smile:

So, I have gathered manual readings of my energy meter of the last years, the input data looks like the following:

type,timestamp,meterId,obisCode,value
energy,2024-03-06T09:07:00+01:00,1HLY…,1-0:1.8.0,4055
energy,2024-03-04T16:11:00+01:00,1HLY…,1-0:1.8.0,4043
energy,2024-02-27T21:00:00+01:00,1HLY…,1-0:1.8.0,4004
energy,2024-02-26T14:25:00+01:00,1HLY…,1-0:1.8.0,3995
energy,2024-02-14T11:11:00+01:00,1HLY…,1-0:1.8.0,3916
energy,2024-02-09T12:00:00+01:00,1HLY…,1-0:1.9.0,3893
energy,2024-02-02T12:18:00+01:00,1HLY…,1-0:1.8.0,3846
energy,2024-01-24T21:27:00+01:00,1HLY…,1-0:1.8.0,3785
energy,2023-12-31T12:00:00+01:00,1HLY…,1-0:1.9.0,3592
energy,2023-07-31T12:00:00+01:00,1HLY…,1-0:1.9.0,2577
energy,2023-02-28T12:00:00+01:00,1HLY…,1-0:1.9.0,1607
energy,2023-02-09T19:12:00+01:00,1HLY…,1-0:1.8.0,1459
energy,2023-02-05T13:44:00+01:00,1HLY…,1-0:1.8.0,1421
energy,2023-01-31T12:00:00+01:00,1HLY…,1-0:1.9.0,1408
energy,2023-01-15T12:00:00+01:00,1HLY…,1-0:1.9.0,1323
energy,2023-01-02T12:00:00+01:00,1HLY…,1-0:1.9.0,1253
energy,2023-01-01T12:00:00+01:00,1HLY…,1-0:1.9.0,1243
energy,2022-06-01T11:15:00+02:00,1HLY…,1-0:1.8.0,0

What I have

For now I have simply plotted these on a visualization:

(green is those matching 1-0:1.8.0, i.e. those actually read from the energy meter; yellow is the values my energy provider assumed (those matching 1-0:1.9.0).

What I want

I “think” I now want a gauge that shows me my average daily consumption. Since I can interact using the time range pickers this might be interesting. I also know and understand that the line is pretty linear already and said daily avg would not be very insightful, but I’m training and want to understand the basics.

Now, if I had a lot of data points I already saw I would group by the day() part of the given timestamps. However, I don’t have multiple time stamps per day. I would need to rely on the interpolation we already see in the graph above, but on a mathematical way.
How would I express this in Grafana?
I would need the differences between two data points, i.e. for example

4055 kWh @ 2024-03-06 — 3995 kWh  @ 2024-02-26
=
60 kWh (in) 10 days 

→ that would be 6 kWh per day for this case. But of course I want this for arbitrary dates in between where no data points exist. Grafana would have to “make them up”, i.e. interpolate them…

My setup for now is:

(you can disregard the transformations, I tried to make something useful but didn’t work as you might see).

Thank you so much for a kick start!