Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
murillocosta
Helper I
Helper I

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

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

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

1.png

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
    )

 

View solution in original post

22 REPLIES 22
tamerj1
Super User
Super User

Hi @murillocosta 
There was a small mistake in the code in the 4th value

2.png

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.

1.png

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

Hi 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/4r0p0e

@murillocosta 
Here you go

2.png

aj1973
Community Champion
Community Champion

Hi @tamerj1 

Please how did you generate your Fibonacci Table?

Can you attach it?

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Hi @aj1973 

The whole idea is complex. Even I don't remember all details. It is even more complex to explain. I was planning to leave an article about this subject in the forum but then I did not see much demand to such approach. Perhaps I'll think again. I'll keep you updated. 

aj1973
Community Champion
Community Champion

Thank you very much, I should admit that I am really impressed.

Good work,

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Hey 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?

@murillocosta 

I have updated the solution, have you checked? Actually, you csnnot find this table anywhere else. This is something I have created out of a mathematical research and reasoning inspired by the Fibonacci numerical series. Perhaps you are the first one on earth to have a real business Power Bi report that performs real recursive calculations. 

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

 

1.png2.png3.png

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?

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. 

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

@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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thank you both @Greg_Deckler and @tamerj1 for your replies. I'll read your link and keep working on what I need. 

 

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?

 

murillocosta_0-1678270071407.png

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

 

Thank you . It's really helpful, I will use this for my projects for recursive calculations. I always try to perform this type of calculation on dbt which is the tool I use for transformation before pulling the data into PBI but some case I only have the data uploaded to PBI.

 

I think you uploaded the wrong file. Could please send my sample file updated?

 

Thanks

@murillocosta 
Apologies for that. The attachment in my previous reply has been updated. Please check.

@murillocosta 

Check my 2nd solution based on dynamic selection of the number of periods. I will apply over your sample file and share it with you. Probably not today as today I'm out of office the whole day

tamerj1
Super User
Super User

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

1.png

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
    )

 

Thanks for that tamerk1. I will try to adapt to my case.

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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