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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JoeriT
Frequent Visitor

Recursion/Self referencing & year-to-date attribution effect

Hello Fabric Community,


I hope you're all doing well! I'm currently working on a project where I need to calculate the year-to-date cumulative attribution effect for investment portfolio analysis in Power BI, and I've encountered a bit of a roadblock.

Here's a breakdown of the problem:


I have a formula that calculates the Year-to-date cumulative attribution effect:

Ei' = (Ei-1')(1+BRi/100) + (Ei)(1+PRi-1'/100)

where:

  • Ei' is the cumulative attribution effect through period i.
  • Ei-1' is the cumulative attribution effect in period i-1.
  • BRi is the benchmark return in period i.
  • Ei is the attribution effect in period i.
  • PRi-1' is the cumulative portfolio return through period i-1.

Note that on the 1st of January no historical data should be used in this formula (even though the dataset contains data from multiple years):

Ei' = (0)(1+BRi/100) + (Ei)(1+0/100)

Thus on January 1st:

Ei' = Ei


I find it difficult to implement the logic correctly, particularly the self-referencing. I read in other posts that a work-around for self-referencing in formulas is to make the formula closed-form. Could anyone provide guidance on the possibility of making this formula closed form and if possible, how to write a code for this formula that works in DAX?


Below you find some sample data and in column G the excel calculation for this formula.

 ABCDEFG
1 DatePort. Total ReturnCumu. Port. Total ReturnBench. Total ReturnAttribution effectCumulative attribution effectExcel formula
21-1-2024-0,282-0,282-0,222-0,001-0,001366‘=E2
32-1-2024-0,13-0,411-0,1760,01630,014863917‘=F2*(1+D3/100)+E3*(1+C2/100)
43-1-2024-0,494-0,903-0,496-0,023-0,00787653‘=F3*(1+D4/100)+E4*(1+C3/100)
54-1-2024-0,177-1,078-0,1810,03590,02771068‘=F4*(1+D5/100)+E5*(1+C4/100)
65-1-2024-0,02-1,098-0,003-0,020,00814329‘=F5*(1+D6/100)+E6*(1+C5/100)
76-1-2024-0,252-1,347-0,222-0,023-0,01474788‘=F6*(1+D7/100)+E7*(1+C6/100)

Note that column G contains the excel formulas I use to calculate column F. I want to replicate the results in column F by dax code.

Any insights or suggestions would be greatly appreciated!

 

Thank you in advance for your help.

2 REPLIES 2
v-yilong-msft
Community Support
Community Support

Hi @JoeriT ,

I create a table as you mentioned.

vyilongmsft_0-1713333573976.png

Then I create a column and here is the DAX code.

Column =
VAR CurrentDate = 'Table'[Date]
VAR CurrentAttributionEffect = 'Table'[Attribution effect]
VAR CurrentBenchmarkReturn = 'Table'[Bench. Total Return]
VAR CurrentPortfolioReturn = 'Table'[Cumu. Port. Total Return]
VAR PreviousCumulativeAttributionEffect =
    CALCULATE (
        SUMX (
            FILTER ( ALL ( 'Table' ), 'Table'[Date] < CurrentDate ),
            'Table'[Cumulative attribution effect]
        ),
        ALL ( 'Table' )
    )
RETURN
    IF (
        CurrentDate = DATE ( YEAR ( CurrentDate ), 1, 1 ),
        CurrentAttributionEffect,
        PreviousCumulativeAttributionEffect * ( 1 + CurrentBenchmarkReturn / 100 ) + CurrentAttributionEffect * ( 1 + CurrentPortfolioReturn / 100 )
    )

Finally you can get what you want.

vyilongmsft_1-1713333706168.png

 

 

 

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yilong-msft 

Thank you for your reply.

 

My apologies. I did not include the row numbers in the sample data. I edited this now. Column G is just to clarify how I calculate Column F. I want to write a dax code that calculates Column F. 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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