I was trying to work out best way to use historical data i had for my energy usage/cost sensors to forecast projected costs for the current month, and came up with the following. (the graph only shows part of a month as i started collecting data a few weeks back)
So, i have a Sensor that records energy usage via EmonPi, i then do some calculates to get a daily cost Sensor…
- platform: template
sensors:
energy_import_cost_2022_3:
friendly_name: ‘Import Cost 2022-2023’
icon_template: mdi:home-import-outline
unit_of_measurement: ‘£’
value_template: “{{ (states(‘sensor.energy_imported_daily_kwh’) | float * 0.33018 + 0.45769) | round(2) }}”
i then created an SQL sensor to calculate for the current month, what the average cost is per day
SELECT *, AVG(dailymax) AS month_average
FROM (
SELECT id,metadata_id,date(created) AS date, strftime(’%Y-%m’, created) year_month, strftime(’%m’,created) month, max(mean) AS “dailymax”, strftime(’%m’,date(‘now’)) AS todays_month
FROM “statistics”
WHERE metadata_id=107 AND month=todays_month
GROUP BY date
ORDER BY created DESC) daily_max;
the metadata_id=107 is the unique id for the sensor i’d created, as recorded in the SQlite database
now i know for the current month what my average daily costs is, i just use that to multiple by number of days in the month. first work out number of days in current month
- platform: template
sensors:
days_in_month:
value_template: >
{% if now().month in [1,3,5,7,8,10,12] %}
31
{% elif now().month in [4,6,9,11] %}
30
{% elif now().month == 2 and ((now().year-2000) % 4 > 0) %}
28
{% elif now().month == 2 and ((now().year-2000) % 4 == 0) %}
29
{% endif %}
then use this to make the forecasted figure for the whole month
- platform: template
sensors:
monthly_energy_cost_forecast:
friendly_name: “Monthly Energy Cost Forecast”
unit_of_measurement: ‘£’
value_template: >
{{ (states(‘sensor.sql_months_average_cost’)|float * states(‘sensor.days_in_month’)|float) |round(2) }}
hope this helps out others who maybe want to check historical data to calculate projections on costs etc.