Forecasting future months' total electricity usage using latest month's data and change in volume

[pickup18]

Hi all,

I have a table for actual electricity consumption that is limited to up to the latest completed month and production volume that also indludes forecast up to the end of the year

Electricity Actuals:

Module	PERIOD	Total KWH
HUB	July 1, 2024	50
DHU	July 1, 2024	50

Production VolumeF

			Is forecast?
HUB	July 1, 2024	50	Actual
DHU	July 1, 2024	50	Actual
HUB	August 1, 2024	60	Forecast
DHU	August 1, 2024	60	Forecast
HUB	September 1, 2024	50	Forecast
DHU	September 1, 2024	50	Forecast

I already have measures for: [Total Actual Electricity Consumption], [Total Volume], 
[MoM Change in Volume] 

what i want is to forecast kwh for the next months based on change in volume to be used in my line chart. it will initially base the forecast from the latest actuals then the following forecast will be based on the result of previous month

The logic/pattern will be like this:
if my latest actuals is for july then august will be based on multiplying july total kwh by change in volume (MoM Change in Volume) in july vs august; then september will be based by getting the total kwh for august and multiplying it with the change in volume august vs september, and so on

Hence from my example table above this will be the result:

Period	Total volume	MoM change in volume	Total KWH
July 1, 2024	100		100
August 1, 2024	120	120%	120 (120%*100)
September 1, 2024	100	83%	100 (120*83%)

Thank you so much!!

[v-xuxinyi-msft]

Hi @pickup18 

Thanks for the reply from bhanu_gautam .

@pickup18 , if your problem is not solved, please try the following measures.

Total volume = CALCULATE(SUM([KWH]), ALLEXCEPT('Production VolumeF', 'Production VolumeF'[Period]))

MoM change in volume = 
VAR _earlier = MONTH(MAX([Period]))
VAR _ealierKWH = CALCULATE([Total volume], FILTER(ALL('Production VolumeF'), MONTH([Period]) = _earlier - 1))
RETURN
DIVIDE([Total volume], _ealierKWH)

Total KWH = 
VAR _earlier = MONTH(MAX([Period]))
VAR _ealierKWH = CALCULATE([Total volume], FILTER(ALL('Production VolumeF'), MONTH([Period]) = _earlier - 1))
RETURN
_ealierKWH * [MoM change in volume]

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

@pickup18 , First create a Calculated Column for MoM Change in Volume:

DAX
MoM Change in Volume =
VAR CurrentMonthVolume = 'Production VolumeF'[Total Volume]
VAR PreviousMonthVolume =
CALCULATE(
SUM('Production VolumeF'[Total Volume]),
DATEADD('Production VolumeF'[PERIOD], -1, MONTH)
)
RETURN
DIVIDE(CurrentMonthVolume, PreviousMonthVolume, 1)

Then create a measure 

Forecasted KWH =
VAR LatestActualMonth =
CALCULATE(
MAX('Electricity Actuals'[PERIOD]),
'Electricity Actuals'[Total KWH] <> BLANK()
)
VAR LatestActualKWH =
CALCULATE(
SUM('Electricity Actuals'[Total KWH]),
'Electricity Actuals'[PERIOD] = LatestActualMonth
)
VAR CurrentMonth = MAX('Production VolumeF'[PERIOD])
VAR MoMChange =
CALCULATE(
AVERAGE('Production VolumeF'[MoM Change in Volume]),
'Production VolumeF'[PERIOD] = CurrentMonth
)
VAR PreviousMonthKWH =
CALCULATE(
[Forecasted KWH],
DATEADD('Production VolumeF'[PERIOD], -1, MONTH)
)
RETURN
IF(
CurrentMonth <= LatestActualMonth,
LatestActualKWH,
PreviousMonthKWH * MoMChange
)

Create a Line Chart:
Use the PERIOD column for the X-axis.
Use the Forecasted KWH measure for the Y-axis.

[pickup18]

Hi @bhanu_gautam ! thank you for your time. I would just like to ask where is this Forecasted KWH measure from?