Forum Discussion
Forecasting future months' total electricity usage using latest month's data and change in volume
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.
Hi bhanu_gautam ! thank you for your time. I would just like to ask where is this Forecasted KWH measure from?