cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.

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

Super User

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

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors