Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

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

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?



Frequent Visitor

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.

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 )
    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 
            Result +  CurrentConfirmed


Super User
Super User

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.

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors