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. A B C D E F G 1 Date Port. Total Return Cumu. Port. Total Return Bench. Total Return Attribution effect Cumulative attribution effect Excel formula 2 1-1-2024 -0,282 -0,282 -0,222 -0,001 -0,001366 ‘=E2 3 2-1-2024 -0,13 -0,411 -0,176 0,0163 0,014863917 ‘=F2*(1+D3/100)+E3*(1+C2/100) 4 3-1-2024 -0,494 -0,903 -0,496 -0,023 -0,00787653 ‘=F3*(1+D4/100)+E4*(1+C3/100) 5 4-1-2024 -0,177 -1,078 -0,181 0,0359 0,02771068 ‘=F4*(1+D5/100)+E5*(1+C4/100) 6 5-1-2024 -0,02 -1,098 -0,003 -0,02 0,00814329 ‘=F5*(1+D6/100)+E6*(1+C5/100) 7 6-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.