Predicted value based on current and previous regulatory year

leo_89 · May 1, 2021, 11:16am

Hi there,

I am trying to show the predicted value of upcoming months based on the average of last 5 values. The regulatory year starts in April and Finishes in March.

The way I am calculating forecast value is the sum of the total value of months of current regulatory years and the last 5-year average value of remaining months.

For clarification, it would be like as follow

The predicted value of upcoming months = total value of current regulatory year + last 5 years average value of remaining months of the regulatory year.

For example, In April current regulatory year starts, so the predicted value of remaining months = actual value of April + last 5 years average value of remaining months (i.e. May to March).

I also don’t want to show the predicted value of months that are gone. For example, I don’t want to show the predicted value of April as it is gone.

To represent the above concept, I wrote the following measure:

Forecast Value =

Var _CYMonth = MONTH(TODAY())

Var _RYMonth = if(_CYMonth > 3 && _CYMonth <= 12, _CYMonth - 3,_CYMonth + 9)

Var sum_last_months = CALCULATE([Normalized Value],FILTER(‘Date’,‘Date’[Date]<=EOMONTH(EOMONTH(TODAY(),0),-1)&&‘Date’[Date] >= [Current_RY_Yr_Strt]))

Var average_remain_month_value = CALCULATE([Average Normalized Value -5y],FILTER(ALLSELECTED(‘Date’),MAX(‘Date’[RY Month Number])>=_RYMonth&&AND(‘Date’[RY Month Number]<=MAX(‘Date’[RY Month Number]),‘Date’[RY Month Number]>=_RYMonth)))

Var total_estimate = average_remain_month_value + sum_last_months

Return

IF(not(ISBLANK(MAX(‘Date’[RY Month Number]))) && MAX(‘Date’[RY Month Number])>=_RYMonth,total_estimate,BLANK())

When I tried to plot the above measure in the line chart, it does not add the value of last month of current year value as shown in the following picture:

The value of the last month of the current month is 7. So the predicted value of May should be 7 + 2.68 (based on last 5 year).
However, in figure it only shows 2.68 not 7+2.68.
Could anyone guide me where I am making the mistake? Sample file here

deltaselect · May 2, 2021, 12:15am

Hi Leo_89,
Just to clarify:
You write “The predicted value of upcoming months = total value of current regulatory year + last 5 years average value of remaining months of the regulatory year.”

Does that mean when you are in December, you want to forecast January till March (RY), based upon the total averages of the actual month April till November of the current year and the history of the month January till March for the last 5 years ?
And if e.g. you are in April, you want the history of May till March (end of RY) for the last 5 years ?
Do you incorporate the MTD values of the current month as well ?

Do you calculate an average amount per day, which is valid for all remaining months to be forecasted ?

I think to work with dynamic rolling rolling periods, therefore I need to understand exactly what is to be taken for the calculation.
You are working with selections of periods for the calculation, which should work fine.

So far, I do not know why your formula does not provide the right graph.

leo_89 · May 2, 2021, 12:46am

@deltaselect, Thanks for the reply.

Yes, you are right. When I am in December, I forecast From January till March based on upon the total average of actual Month from April till November of current year and history of January till March of last year.

Yes, you are right that when we are in April, we want the history of May till March (end of RY) for the last 5 years.

No, I don’t incorporate the MTD values of the current month. To be honest, I did not think about it. I think it would be good to have.

No, I calculate the average amount per month. The data is available at fine grained level (i.e. half-hour time). I sumed up half-hourly data and take the average based on the month.

Your help to fix the issue would be really appreciated. Thanks

deltaselect · May 2, 2021, 12:21pm

Hi Leo,

I looked at your formulas, above, and your clarification. And have set up an overview in Excel how I understand the calculation should be made per month (see attached Excel).

If you want to calculate the forecast per month, maybe the second version is an option (easy to calculate) ? Please let me know your thoughts.

Setup calculation Forecast Value Measure.xlsx (11.6 KB)

Another question : with the forecasting, is it sufficient to report per month ? (Not per day?). Formulas like DATEADD(DateT[Date],-1,MONTH) for the previous month gives the daily amount of the previous month, if reported on a daily base, and the monthly amount if reported on a monthly base, which (may) change the intended average calculations.

Kind regards, Delta Select

leo_89 · May 2, 2021, 7:22pm

Hi @deltaselect,

Thanks for sharing file. I think second calculation does make sense. I am also trying to calculate in my pibx file in similar way. The problem I have is that it does not display on line chart the way I want.
Forecasting is to get rough estimation of upcoming months. But I think per day or week would be even better. As my values are also based on season of the year. Do you think that looks at the previous month for the same day would be a good approximation?

deltaselect · May 3, 2021, 2:59am

Hi Leo_89,

The second calculation makes it easier to calculate a forecast.
An average forecast of one day (5 previous years values x 48 epochHH) fluctuates obviously more then an average forecast of a month ( 5 previous years values x 48 x +/- 30), but given the 48 epochHH, a daily forecast will work, I think.

Attached a first work out, with an forecast calculation, based upon 5 years history values, the MTD is still to be added, and I will do some more auditing on the calculated values.

With this setup, you can report forecasts based upon different period selections, like days, months, also for future periods and past periods (the calculation method remains the same)

As the relationship between the date table months and the RY is clear, I think using a separate RY column in the date table will solve your need to report in Reporting Year periods. The date table has already a Fiscal Year in it, with currently starts at Oct, this can be changed easily to April. (hence no need to adjust the forecast formulas for the difference between date periods and RY periods)

I have used my own file with 150.000 random excel values =RAND()*1000 = average 500

Hopes this helps, looking forward for comments.
Kind regards, deltaselect

deltaselect · May 3, 2021, 9:55pm

Hi Leo,

Please receive hereby the revised version, calculating averages based upon MTD and 5 year history. Please have a look at the measure to see how it is developed, it can be used per day, per week, per month, and as contains data of periods till 5 years backwards, you can forecast for some years in the future.

The only thing I did not incorporate are the RY periods, which can easily be implemented in the date table.

Average calculation KPI.pbix (2.5 MB)

See the PBIX for more details, attached are some very basic reports.
If you might have questions, feel free to ask.
Kind regards, Delta Select