Forum Discussion
Recursive Calculation and Forecast measure
Hi murillocosta
Attached your sample file updated with the dynamiuc solution
Total Outbound Final 2 =
VAR Periods = [Number of Periods Value]
VAR TotalOutBound = [Total Outbound]
VAR W = CALCULATE ( MAX ( 'SUMMARIZE'[Week Rank] ), 'SUMMARIZE'[Total Outbound] <> 0, REMOVEFILTERS ( ) )
VAR CW = MAX ( 'SUMMARIZE'[Week Rank] )
VAR SelectedWeeks = CALCULATETABLE ( VALUES ( 'SUMMARIZE'[Week Rank] ), ALLSELECTED ( ) )
VAR WeeksOnAndBefore =
FILTER(
SelectedWeeks,
'SUMMARIZE'[Week Rank] > W && 'SUMMARIZE'[Week Rank] <= CW
)
VAR CurrentIndex = COUNTROWS ( WeeksOnAndBefore )
VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 1, Periods, 1 ), "@ValueInex", [Value] )
VAR T2 =
ADDCOLUMNS (
T1,
"@Value",
VAR ValueIndex = [@ValueInex]
RETURN
CALCULATE ( [Total Outbound], 'SUMMARIZE'[Week Rank] = 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
Result + TotalOutBound
Hello, tamerj1
I stumbled upon this post while searching for a solution similar to the one you provided. I need to calculate data based on previously calculated data using the same metric. Your solution is truly impressive! Thank you for sharing it.
Now, I'm working on adapting it to my needs, and I'm hopeful that I'll manage to make it work. Regarding the Fibonacci_Table, is that the only method available for performing such calculations?
- tamerj12 years ago
Community Champion
Hi vitorcampos
Glad that you found that helpful.
Regarding your question, to be honest I have not been following up on this subject for some time. I'm not aware of any other method. In fact he Fibonacci table is some that I have invented couple of years ago to tackle this issue of recursion following mathematical approach.
As far as I know Greg_Deckler was following up on this subject since long time ago. I believe no one can advise on this subject better than he can do.- Greg_Deckler2 years ago
Community Champion
tamerj1 vitorcampos Yeah, true recusion is a pain and doesn't work: Previous Value (“Recursion”) in DAX – Greg Deckler
That article contains a bunch of links to various methods I've tried.
- vitorcampos2 years agoFrequent Visitor
Thank you both Greg_Deckler and tamerj1 for your replies. I'll read your link and keep working on what I need.
- vitorcampos2 years agoFrequent Visitor
Hello again, tamerj1.
I've made the necessary adjustments for my project based on your fantastic metric.
However, I'm currently struggling with creating a Year-to-Date metric by combining real and projected values.
Could you provide me with a clue on how to formulate a YTD measure that incorporates both types of data (real and projected ones)?