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
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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