moving average also to the future

[Norbertus123]

Hi,

i have the next simple table:
01-01-2024 => 4

01-02-2024 => 6

01-03-2024 => 5
01-04-2024 => 8

Now i want the next solution

* for periode 01-05-2024 the average of the last 3 months (6+5+8)/3 = 6.33
* for periode 01-06-2024 the average of (5+8+6.33)/3 = 6.44
* for periode 01-07-2024 the average of (8 +6.33 + 6.44)/3 = 6.93
* for periode 01-08-2024 the average of (6.33+6.44+6.93)/3 = 6.56

end so on. Is that possible?

with kind regards

Norbertus

[Norbertus123]

hi @rajendraongole1  Thanks for your reaction. This solution gives not exactly what i hoped for:

For period 5 i want the moving average of period 2 + 3 + 4

For period 6 i want the average of period 3 + 4 + moving average period 5

For period 7 i want the average of period 4 + moving average period 5 + moving average period 6

For period 8 i want the average of moving average period 5 + moving average period 6 + moving average period 7 

* for periode 01-05-2024 the average of the last 3 months (6+5+8)/3 = 6.33 
* for periode 01-06-2024 the average of (5+8+6.33)/3 = 6.44
* for periode 01-07-2024 the average of (8 +6.33 + 6.44)/3 = 6.93
* for periode 01-08-2024 the average of (6.33+6.44+6.93)/3 = 6.56

[rajendraongole1]

Hi @Norbertus123 - Calculate a rolling average in Power BI that incorporates forecasted values for future periods based on the average of the last three months

base measure to calculate the sum of sales

TotalSales = SUM('Financials'[Sales])

Create a measure to calculate the rolling average of the last three months.

RollingAverage3Months =
VAR CurrentDate = MAX('Financials'[Date])
VAR RollingAverage =
AVERAGEX(
DATESINPERIOD(
'Financials'[Date],
CurrentDate,
-3,
MONTH
),
[TotalSales]
)
RETURN
RollingAverage

Create rolling average for forecasted periods measure

ForecastedRollingAverage =
VAR CurrentDate = MAX('Financials'[Date])
VAR Last3Months =
CALCULATETABLE(
TOPN(3,
ADDCOLUMNS(
SUMMARIZE('Financials', 'Financials'[Date]),
"Sales", [TotalSales],
"RollingAvg", [RollingAverage3Months]
),
'Financials'[Date], DESC
),
FILTER('Financials', 'Financials'[Date] < CurrentDate)
)
VAR AvgLast3Months =
IF(
COUNTROWS(Last3Months) < 3,
BLANK(),
AVERAGEX(Last3Months, [RollingAvg])
)
RETURN
IF(
CurrentDate <= MAX('Financials'[Date]),
[RollingAverage3Months],
AvgLast3Months
)

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!