How to frame L3M moving average logic for the future months to go in a year using

[priya_rajendran]

Hi,

I am trying to create a simple measure to project the sales for the future months in a year by using a simple last 3 months rolling average logic. However I am stuck here.

For the current month, say sep'22 - I am able to get the L3M by calculating sum(may+jun+jul sales)/3.

But for Oct'22, I want (sum(jun+jul sales) + the extrapolation that i did for Sep'22 above)/3 which i am not able to achieve.

I have explained my required logic in the snap below.

Appreciate your help!

[v-easonf-msft]

Hi， @priya_rajendran 

Compared to excel, PowerBI is currently not good at handling this kind of recursion.

For the example you posted, even if the recursive formula could be determined, I think it would be complicated and hard to understand.

Best Regards,
Community Support Team _ Eason

[tamerj1]

Hi @priya_rajendran 
I came across this post and I thought I have solved many recursive calculation problems but none of them related to forecast. The problem with forecast is that the future data does not actually exist so the calculation is completely recursive. My usual approach is to detect the pattern of recursion and utilize this pattern to produce an alternative method of calculation. However, the completely recursive calculation produces a pattern (sequence) that cannot be defined mathematically. In mathematical, this sequence is called the "The Fibonacci sequence". It is not a complex subject for mathematicians but I don't think anyone in this forum would like to learn about it.
The bottom line is that fully recursive calculation is actually possible in Power Bi by introducing a The Fibonacci sequence table to the data model. This will require me to write a detailed article to explain every aspect of that although it is not complex at all, it is just completely different.

Of course for your case more DAX shall be required to consider the different levels for the matrix hierarchy. The good thing about this approach is that it is fast in terms of performance.

[AlexisOlson]

@tamerj1, you don't even need a separate table if you use some mathemagical dark arts!

Try this out:

Forecast = 
VAR LastDateWithData = CALCULATE ( MAX ( Sales[Order Date] ), REMOVEFILTERS ( ) )
VAR LastMonthWithData = YEAR ( LastDateWithData ) * 100 + MONTH ( LastDateWithData )
VAR CM = MAX ( 'Date'[Year Month Number] )
VAR M = CALCULATE ( MAX ( 'Date'[Year Month Number] ), 'Date'[Year Month Key] = LastMonthWithData, REMOVEFILTERS() )
VAR S3 = CALCULATE ( [Sales Amount], 'Date'[Year Month Number] = M, ALLSELECTED ( ) )
VAR S2 = CALCULATE ( [Sales Amount], 'Date'[Year Month Number] = M - 1, ALLSELECTED ( ) )
VAR S1 = CALCULATE ( [Sales Amount], 'Date'[Year Month Number] = M - 2, ALLSELECTED ( ) )
VAR SelectedMonths = ALLSELECTED ( 'Date'[Year Month Number] )
VAR MonthsOnAndBefore = FILTER ( SelectedMonths, 'Date'[Year Month Number] >= M && 'Date'[Year Month Number] <= CM )
VAR R = COUNTROWS ( MonthsOnAndBefore )
VAR W  = PI() - ATAN ( SQRT( 2 ) )
VAR T  = POWER ( 3, ( R - 2 ) / 2 )
VAR S  = POWER ( 3, ( R - 1 ) / 2 )
VAR D  = POWER ( 3, R )
VAR F1 =   0.5 * T * ( 3 * T - COS (   R       * W ) + SQRT ( 2 ) * SIN (   R       * W ) )   / D
VAR F3 =   0.5 * S * ( 3 * S - COS ( ( R + 1 ) * W ) + SQRT ( 2 ) * SIN ( ( R + 1 ) * W ) )   / D
VAR F2 = ( 3 * T * T - 0.5 * T * ( 2 * COS ( R * W ) + SQRT ( 2 ) * SIN (   R       * W ) ) ) / D 
RETURN
    IF ( R > 0, S1 * F1 + S2 * F2 + S3 * F3 )

[Anonymous]

This is such a creative way to forecast. I am going down this rabbit hole to complete my request, but mine involves showing the aggregate of forecast too. Thanks for this.

[Greg_Deckler]

@priya_rajendran Try:

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

[priya_rajendran]

Hi @Greg_Deckler , thanks for your response. But still this doesnt work because my table has sales data only untill Aug-2022. And for the future month say for. eg. Jan 23. the date filter is from oct to dec 22 where i have no sales data. So the measure gives bank result for Jan 23. It should pick the previous L3M that the measure calculated for oct, nov, dec 2022 instead getting it from the sales table.

Snapshot below. appreciate our help!

[Greg_Deckler]

@priya_rajendran Ah, its recursive. Sorry, out of luck. Previous Value (“Recursion”) in DAX – Greg Deckler