Forum Discussion
Recursive Calculation and Forecast measure
Hi murillocosta
Such fully recursive problems cannot be handled by DAX straight forward. A helper table that suits your case shall be needed to handle the recursion. The solution is a bet difficult to understand and it should be carefully design to suit each case independently (cannot be generalized) nevertheless, what ever the chosen details of the solution, the general idea remains the same.You need to import the Fibonacci table as is and use the DAX as is. However, your real data might not be as I expected, therefore some changes might be required.
Please note that such reports should have limited flexibility in terms of the columns used to slice. Slicing by different column(s) might require changing the code. You need to understand that this is a limitation of the DAX language for the time being, hopping future updates will curry new features of the language such as loops that can help performing recursive calculations much more easier.
Also Please note that there is a rounding error with this method and the results shall not be 100% accurate.
Please refer to the sample file with the solutionForecast Count = VAR W = CALCULATE ( MAX ( 'Table'[Year Week Sort] ), REMOVEFILTERS ( ) ) -- with real data would be VAR LastDateWithData 'Table'[Date] and then VAR W = YEAR ( LastDateWithData ) * 100 + WEEKNUM ( LastDateWithData ) VAR CW = MAX ( 'Date'[Year Week Sort] ) VAR S4 = CALCULATE ( [CounRef], 'Date'[Year Week Sort] = W, ALLSELECTED ( ) ) VAR S3 = CALCULATE ( [CounRef], 'Date'[Year Week Sort] = W - 1, ALLSELECTED ( ) ) VAR S2 = CALCULATE ( [CounRef], 'Date'[Year Week Sort] = W - 2, ALLSELECTED ( ) ) VAR S1 = CALCULATE ( [CounRef], 'Date'[Year Week Sort] = W - 3, ALLSELECTED ( ) ) VAR SelectedWeeks = ALLSELECTED ( 'Date'[Year Week Sort] ) VAR WeeksOnAndBefore = FILTER ( SelectedWeeks, 'Date'[Year Week Sort] > W && 'Date'[Year Week Sort] <= CW ) VAR Ranking = COUNTROWS ( WeeksOnAndBefore ) RETURN SUMX ( FILTER ( FibonacciTable, FibonacciTable[Index] = Ranking ), FibonacciTable[Value1] * S1 + FibonacciTable[Value2] * S2 + FibonacciTable[Value3] * S3 + FibonacciTable[Value3] * S4 )murillocosta
Here you go
Hi murillocosta
There was a small mistake in the code in the 4th value
However, I've created another solution that is more flexible. You can Chose the number of averaging periods (from 2 up to 12). Ofcourse this can be a parameter selected by the user.
Forecast Count 2 =
VAR Periods = 4
VAR W = CALCULATE ( MAX ( 'Table'[Year Week Sort] ), REMOVEFILTERS ( ) )
VAR CW = MAX ( 'Date'[Year Week Sort] )
VAR SelectedWeeks = ALLSELECTED ( 'Date'[Year Week Sort] )
VAR WeeksOnAndBefore = FILTER ( SelectedWeeks, 'Date'[Year Week Sort] > W && 'Date'[Year Week Sort] <= CW )
VAR CurrentIndex = COUNTROWS ( WeeksOnAndBefore )
VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 1, Periods, 1 ), "@ValueInex", [Value] )
VAR T2 =
ADDCOLUMNS (
T1,
"@Value",
VAR ValueIndex = [@ValueInex]
RETURN
CALCULATE ( [CounRef], 'Date'[Year Week Sort] = W + ValueIndex - Periods, ALLSELECTED ( ) )
)
VAR Result =
SUMX (
T2,
VAR CurrentValue = [@Value]
VAR FibTable =
FILTER (
Fibonacci_Table,
Fibonacci_Table[Index] = CurrentIndex
&& Fibonacci_Table[Period Index] = Periods
&& Fibonacci_Table[Value Index] = [@ValueInex]
)
VAR FibValue = MAXX ( FibTable, Fibonacci_Table[Value] )
RETURN
CurrentValue * FibValue
)
RETURN
ResultHi tamerj1, thanks for that.
I've been tryingo to get it done for hours but still couldn't figure out why on my scenary is not working.
Would you be able to check my file below and help me out?
https://easyupload.io/4r0p0emurillocosta
Here you goHey tamerj1, just one more question. If I need to change the average from 4 to n.....how would you update the fibonacci table values????is there any place you can get it from?
