moving average also to the future

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!!