How to create weekly forecast for next xx weeks based on historical data? Recursive Calculation

[NebojsaZ88]

Hello community,

Need help here. I have a table with following columns, where last one utilization is just simple ratio between Confirmed and Reservations. In table, a have always values for Reservations for next 12-16 weeks (in future) but for Confirmed, only up to current week. Now I need to predict for next 4 week Confirmed values based on historical data, as following:
For first next week 2022-12-17, Confirmed should be average of Confirmed for previous 4 weeks (2022-11-19 to 2022-12-10). Result shoud be 8.650.

Then, for 2022-12-24, Average is SUM of 8.650 + previous 3 weeks (8.800 + 8.700 + 8.600), and so on.

Any advice?

[NebojsaZ88]

Hi @tamerj1 , I will try to explain in details structure, because I can not share sample.
So, imagine only two tables, first one is Calendar and second with data from which i created measures: Reservations (CALCULATE(SUM XX) ), Confirmed (CALCULATE(SUM XX) ) and Utilization (ratio of those 2 measures)
I would prefer measure solution, as table with data contains more than 1 mil rows.

Hope this can help.

[tamerj1]

Hi @NebojsaZ88 
It is a bit complex but it is the only way to force recursion over the DAX engine. The 'FibonacciTable' is table of constant values that can be used in any recursive calculation problem that involves averaging the last 4 values that help us deal with recursion and is very easy to create with excel if you are intersted to learn more about it or otherwise just use as is and don't worry about it. Please refer to attached sample file.

Confirmed 2 = 
VAR T1 = ADDCOLUMNS ( ALLSELECTED ( 'Date'[Week Ending] ), "@Confimed", [Confirmed] )
VAR T2 = FILTER ( T1, [@Confimed] > 0 )
RETURN
    SUMX ( 
        VALUES ( 'Date'[Week Ending] ),
        VAR CurrentWeek = 'Date'[Week Ending]
        VAR CurrentConfirmed = [Confirmed]
        VAR LastWeekWithData = MAXX ( T2, [Week Ending] )
        VAR W = LastWeekWithData
        VAR R4 = CALCULATE ( [Confirmed], 'Date'[Week Ending] = W, ALLSELECTED ( ) )
        VAR R3 = CALCULATE ( [Confirmed], 'Date'[Week Ending] = W - 7, ALLSELECTED ( ) )
        VAR R2 = CALCULATE ( [Confirmed], 'Date'[Week Ending] = W - 14, ALLSELECTED ( ) )
        VAR R1 = CALCULATE ( [Confirmed], 'Date'[Week Ending] = W - 21, ALLSELECTED ( ) )
        VAR T3 = FILTER ( T1, [Week Ending] > LastWeekWithData && [Week Ending] <= CurrentWeek )
        VAR Ranking = COUNTROWS ( T3 )
        VAR Result =
            SUMX ( 
                FILTER ( FibonacciTable, FibonacciTable[Index] = Ranking ),
                FibonacciTable[Value1] * R1 + FibonacciTable[Value2] * R2 + FibonacciTable[Value3] * R3 + FibonacciTable[Value4] * R4 
            )
        RETURN
            Result +  CurrentConfirmed
    )

[tamerj1]

Hi @NebojsaZ88 
This is a fully recursive problem which DAX cannot handle by itself. However I had previously found a solution to this problem but in order to provide you with a proper solution I need more details.

Are you looking for a measure or a calculated table solution? If you're looking for a measure that means the screenshot is actually for a table visual. In this case please provide a sample of the source data table(s) along with the relationships and the DAX for the existing measures used in the same table visual. Thank you.