Forum Discussion

murillocosta's avatar
murillocosta
Helper I
3 years ago
Solved

Recursive Calculation and Forecast measure

Hi,   I have a table containing the value count of rows over a grouped year/week column. I have sorted the data by year/week ascending:   CountRef | Year Week Sort 6                201831 4    ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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 solution

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

     

tamerj1's avatar
tamerj1
Community Champion
3 years ago

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

 

murillocosta's avatar
murillocosta
Helper I
3 years ago

 

Hi tamerj1 

 

Would you be able to help me calculate the "Total Outbound Final 2" for the past records as well instead of just repeating the "Total Outbound" as below?

 

The ideia is to create a compararion of the actual spend vs forecast that was calculated in the past.

 

I tried it today for couple of hours but it seems that it will require to change Fibonacci table.

 

What do you think?

 

For example:

Let suppose we pick 7 for the "Number of Period" so the first 7 rows we would need to repeat the Total Outbound (as we won't have enough data to calculate average) and from the eight row we start calculating it recursively as it's already doing for forecast.

 

Thanks

 

 

 

 

Thanks