Query help - extract first row for a given month / day ignoring the year

Hi community,

I’m looking for some help to enhance the next flux query:

from(bucket: "electricityp1")
  |> range(start: 2022-07-15T00:00:00Z, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "elec_p1")
  |> filter(fn: (r) => r["_field"] == "delivered_tarrif1" or r["_field"] == "delivered_tarrif2" or r["_field"] == "received_tarrif1" or r["_field"] == "received_tarrif2")
  |> first()

It’s currently extracting the record I need but every year I need to update range(start: 2022-07-15T00:00:00Z

What I want to achieve, I’m invoiced every year on the 15th of July and I have a single stat in my dashboard that shows me the index from the day of the last invoice.

If today is the 25th of September 2022 then I want to extract the record from the last 15th of July, which will in this case be the 15th of July 2022.
Of course if today is the 05th of May 2023 then I still want to extract that same last 15th of July from 2022.

I’m pretty sure they must be a more efficient way to extract that first row automatically.

Thanks for your help.

Chris

Hi community,

No one can give me an hint, point me in the right direction?

Thanks

Hello @Chris_Home,
Thanks for bumping feel free to tag me as well next time. Sometimes questions just fall through the cracks.

import "array"
import "experimental/array"
import "date"

returnDate = (myDate) => {
yearDate = if date.month(t: myDate) > 7 then date.year(t: myDate) else (date.year(t: myDate) - 1)
return string(v: yearDate) + "-07-15" }

array.from(rows: [{"year_val": returnDate(myDate: now()) }])
|> yield(name: "this year")

array.from(rows: [{"year_val": returnDate(myDate: 2023-05-10) }])
|> yield(name: "previous year")

Let me know if that works for you!