Forum Discussion

pickup18's avatar
pickup18
Icon for Helper I rankHelper I
2 years ago

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

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:

ModulePERIODTotal KWH
HUBJuly 1, 202450
DHUJuly 1, 202450


Production VolumeF

   Is forecast?
HUBJuly 1, 202450Actual
DHUJuly 1, 202450Actual
HUBAugust 1, 202460Forecast
DHUAugust 1, 202460Forecast
HUBSeptember 1, 202450Forecast
DHUSeptember 1, 202450Forecast


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:

PeriodTotal volumeMoM change in volumeTotal KWH
July 1, 2024100 100
August 1, 2024120120%120 (120%*100)
September 1, 202410083%100 (120*83%)


Thank you so much!!


Need Help
Tutorial Requests

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      pickup18
      Icon for Helper I rankHelper I
      2 years ago

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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.