Calculating value for future month based on previous month

[Anonymous]

Hi, 

I have measure (measure 1) which calculates a value for each month. I wish to calculate a forecast for the coming month based on the previous the average for the last 1, 2 and 3 month but not later then todays month. E.g. 

Month	Measure1	Forecast measure to  be (correct)	My current forecast measure (wrong)
Aug 2020	2
Sept 2020	2
Oct 2020	3
Nov 2020		2,33	2,33 (average of aug, sep, oct)
Dec 2020		2,33	1,67 (average of sep, oct and nov)
Jan 2021		2,33	1 (average of oct, nov and dec)

At the moment I use this forecast measure: 

=

Var

FrvLM = CALCULATE([FraværsdagsværkPrÅrsværk], DATEADD(Calender[Date], -1, MONTH)) 

Var

frv2MAgo = CALCULATE([FraværsdagsværkPrÅrsværk], DATEADD(Calender[Date], -2, MONTH)) 

Var

frv3MAgo = CALCULATE([FraværsdagsværkPrÅrsværk], DATEADD(Calender[Date], -3, MONTH)) 

RETURN

DIVIDE(FrvLM + frv2MAgo + frv3MAgo, 3, 0)

Thanks! 
Mikkel

[DataInsights · Accepted solution]

@Anonymous,

Try these measures:

Total Amount = SUM ( TestData[Amount] )

Forecast = 
VAR vLastDateMeasure = LASTNONBLANK ( ALL ( 'Date'[Date] ), [Total Amount] )  --get last date where measure is not blank
VAR vEndDate1 = EOMONTH ( vLastDateMeasure, 0 )
VAR vStartDate1 = DATE ( YEAR ( vEndDate1 ), MONTH ( vEndDate1 ), 1 )
VAR vEndDate2 = EOMONTH ( vLastDateMeasure, -1 )
VAR vStartDate2 = DATE ( YEAR ( vEndDate2 ), MONTH ( vEndDate2 ), 1 )
VAR vEndDate3 = EOMONTH ( vLastDateMeasure, -2 )
VAR vStartDate3 = DATE ( YEAR ( vEndDate3 ), MONTH ( vEndDate3 ), 1 )
VAR vMonth1Measure =
    CALCULATE (
        [Total Amount],
        DATESBETWEEN ( 'Date'[Date], vStartDate1, vEndDate1 )
    )
VAR vMonth2Measure =
    CALCULATE (
        [Total Amount],
        DATESBETWEEN ( 'Date'[Date], vStartDate2, vEndDate2 )
    )
VAR vMonth3Measure =
    CALCULATE (
        [Total Amount],
        DATESBETWEEN ( 'Date'[Date], vStartDate3, vEndDate3 )
    )
VAR vVisualRowDate = MAX ( 'Date'[Date] )  --get date from row in table/matrix visual
VAR vAverage = DIVIDE ( vMonth1Measure + vMonth2Measure + vMonth3Measure, 3 )
RETURN
    IF ( vVisualRowDate > vEndDate1, vAverage, BLANK () )  --display average for future months

Date table is 'Date', and should be joined to the data table (TestData).

[Anonymous]

Thanks @DataInsights!

It works perfectly. One last adjustment. My "LASTNONBLANK" can be a month in 2021 as month can be filled out in advance. How can I change the the "vLastDataMeasure" to be today instead?

Best,
Mikkel

[DataInsights]

@Anonymous,

Glad to hear that works. You can change it as follows:

VAR vLastDateMeasure = TODAY ()