Transforming data from Prometheus with sparse data from mysql

Using 10.2.2 I have a timeseries set up showing power load from a TP-Link kasa plug as KwH per unit time as I want to plot the cost. This is from prometheus. Now multiplying this by price/KwH is no problem, however that’s very inflexible as hardcoding/setting a dashboard variable changes the entire data when the price changes, e.g.

(kasa_power_load * (15/3600/1000)) * 0.28339

So I have set up a mysql table with timestamps and costs at the point they change. I have a datasource and get the data fine in grafana. However my problem is that the mysql timestamps are obviously not going to align with the prometheus data, and will be very few and far between. It will be months between an energy price change, so I’m trying to transform data based on the last energy price from the mysql table, for the data point e.g.:

Date | Price per KwH
01-01-2023: 0.288
01-03-2023: 0.299
01-05-2023: 0.34

and then use these as a multiplier to transform the data. taking into account that data between price increases… e.g. power will be multiplied by 0.299 on 04-04-2023

I can’t seem to fill in the gaps with the previous price, until the new price. Or have I gone completely off-track, is there a simpler way of adding time-based variables into a grafana dashboard etc? I’d rather not have to delve into the kasa prometheus exporter and export the current cost as well as that’s a lot of repeated data for a 15s scrape.

Any help, no matter how small will be appreciated! Thanks.