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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
NebojsaZ88
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?

NebojsaZ88_0-1670844939990.png

 

3 REPLIES 3
NebojsaZ88
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.

1.png2.png3.png

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
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

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors