Forum Discussion

JoeriT's avatar
JoeriT
Frequent Visitor
2 years ago

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 ...
Attribution effect
DAX
recursion
Self-referencing
YTD
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi JoeriT ,

I create a table as you mentioned.

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.

 

 

 

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.

  • JoeriT's avatar
    JoeriT
    Frequent Visitor
    2 years ago

    Hi Anonymous 

    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.