Forecast Financial Year End figure

[Axiomite]

Hi there, 
I think I have been at it for almost a day now but cannot solve it. 

I would like to display the forecasted Sales figures on the Financial year End date, in this case 1 March 2023. What will the forecasted Amound be

The forecast must be calculated based on the last 12  (full) months avg, suppose rolling will be the one to use?

I have a calendar table and a factTable joined on dates (one to many).

Calendar table (transactions only go up until Sept 2022)
dimDates =

VAR BaseCalendar =

    CALENDAR(DATE(2019,03,01),EOMONTH(LASTDATE(facTable[TxDate]),0))

RETURN

    GENERATE

    (

        BaseCalendar,

        VAR BaseDate = [Date]        

        VAR YearDate = YEAR(BaseDate)

        VAR Quarter = QUARTER(BaseDate)

        VAR MonthNr = FORMAT(BaseDate,"MM")

        VAR MonthNrs = FORMAT(BaseDate,"MM")

        VAR Month = FORMAT(BaseDate, "MMM")

        VAR Week = WEEKNUM(BaseDate)

        VAR Day = DAY(BaseDate)

        RETURN ROW

        (

            "Year", YearDate,

            "Quarter", Quarter,

            "Month Year", Month & " " & YearDate,

            "YearMonth", YearDate & MonthNrs,

            "MonthNr", MonthNr,

            "Month", Month,

            "Week", Week,

            "Day", Day

        )

    )

I will probably have to amend this dimDates to dynamically display until the current financial year end?

Fact table only with transactions until Sept 2022
The factTable with dates and sales amounts daily.

Example:

Any suggestions? 

Kind regards

[Greg_Deckler · Accepted solution]

@Axiomite Try something like:

Better Average per Category = 
    VAR __Table = SUMMARIZE('Table',[Month],"Value",SUM('Table'[Value]))
RETURN
    AVERAGEX(__Table, [Value])

or:

Better Rolling Average = 
    VAR __EndDate = MAX('Table'[Date])
    VAR __MonthsAgo = EOMONTH(__EndDate, -12)
    VAR __StartDate = DATE(YEAR(__MonthsAgo), MONTH(__MonthsAgo), 1)
    VAR __Table = 
        SUMMARIZE(
            FILTER(ALL('Table'),[Date]>=__StartDate && [Date]<=__EndDate),
            'Table'[Month],
            "__Value",SUM('Table'[Value])
        )
RETURN
    AVERAGEX(__Table,[__Value])