Build a graph that displays each month consumption

Grafana Dashboards
1

hi again !

Still working on my electrical dashboard, I have one more question.

I would like to rebuild something like I can see in HA electrical dashboard. where I can see the total consumption for this or that sensor, month by month.

Here is the flux query that do display the very last kWh consumtion for last month (please note I’m using 21h59 due to UTC):

from(bucket: "homeassistant")
  |> range(start: 2023-03-31T21:59:00Z, stop: 2023-03-31T21:59:59Z)
  |> filter(fn: (r) => r["_measurement"] == "kWh")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["domain"] == "sensor")
  |> filter(fn: (r) => r["entity_id"] == "compteur_elec_conso_kwh_totale_nuit_et_jour_monthly")
  |> aggregateWindow(every: inf, fn: last, createEmpty: false)
  |> yield(name: "last")

Now I’d like to figure out the way to build a dashboard that would display bars, for each month of the year, where each bar would display the total kWh usage that was reported at 21h59m59s.

I was thinking about creating 12 different queries, but this makes no sense and would avoid the graph to be automatically generated.
this becomes even more impossible to manually manage if I want to display the daily usage for the whole month!

Can someone help me creating this in the right way ?

In short, this is what I’d like to see in grafana instead of HA :

each day of the month :

image
image2888×762 118 KB

each month of the year:

image
image2894×756 125 KB

Thanks !

2

Hello

Have you looked at the documentation for grouping and tried it?

3

hi @yosiasz !

Can you please direct me to the right tutorial / documentation ?

I do find a lot of different grouping topics and also things related to transformation when I do google for this.

Also, I believe this is a case study, right ? Lots of people must be achieving exactly what I try to do here.

4

Hi @raph1

This is best done through an aggregateWindow statement in Flux, with the “every” parameter set to “1mo” (1 month) and the fn aggregator set to “last”. Then leave your range to be tied to whatever you select in the Grafana drop down picker (or set relative time in the query options).

There are some excellent tutorials on the Flux basics offered by InfluxDB University (all free… just takes some time).

from(bucket: "homeassistant")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "kWh")
|> filter(fn: (r) => r["_field"] == "value")
|> filter(fn: (r) => r["domain"] == "sensor")
|> filter(fn: (r) => r["entity_id"] == "compteur_elec_conso_kwh_totale_nuit_et_jour_monthly")
|> aggregateWindow(every: 1mo, fn: last, createEmpty: false)
|> yield(name: "last")