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                201832

9                201833

13              201834

17              201835

7                201836

15              201837

21              201838

 

I need to calculate the average value across the rolling previous 4 rows. For example, the average across: 21+15+7+17 = 15, THEN

 

15 + 21 + 15 + 7 = 14.5 THEN

 

14.5 + 15  + 21 + 15 = 16.37 ....

 

The trick here is to include the last calculate value with the previous row.

 

Thanks

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

     

23 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    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
    )

     

    • j_palash's avatar
      j_palash
      New Member
      2 years ago

      Hello sir, 

       

      I have a similar problem, but i need to use the moving average for the last 6 data points. Can you please tell me how did you created this fibonacci table and how can i generate it for Value5 & Value6

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    murillocosta , You can create a new table with all Year Week Sort , week number (Assume Date)

     

    Week Rank = Rankx(Date, [Year Week Sort], ,desc,dense)

     

    and new measure

     

    Last 4 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

     

     

    Power BI — Week on Week and WTD
    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

    • murillocosta's avatar
      murillocosta
      Helper I
      3 years ago

      Thanks for the suggestion, I couldn't replicate it thou....Not sure if someone could give an example in a .pbix file. I will keep trying in the meantime.

       

      Many thanks

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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
    Result