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.
DAX implementation of Holt-Winters Additive - Help working around Recursion
Our users love the forecasts that the Power BI visuals provide. However, we need to be able to see the values of the forecasted values in a Power BI visual, i.e. a table, as well as to be able to do more with those values. Sandeep Pawar has a great post about the underlying methodology used by Power BI: ETS(AAA) https://pawarbi.github.io/blog/forecasting/python/powerbi/forecasting_in_powerbi/2020/04/24/timeseries-powerbi.html#How-does-Power-BI-create-the-forecast? Charles Zaiontz from Real-Statistics.com has excellent working models in Excel of Holt-Winters Additive <-> ETS(AAA) Formulas: https://www.real-statistics.com/time-series-analysis/basic-time-series-forecasting/holt-winters-additive/ Excel file with example (see sheet HoltWinters6): https://www.real-statistics.com/wp-content/uploads/2022/03/Real-Statistics-Time-Series-Examples.xlsx The method is, unfortunately for enthusiastic but (so far) unsuccessful DAX practitioners, *recursive* Having read through all of Greg_Deckler 's writing on working around implementing recursion in DAX, as well as AlexisOlson 's great StackOverflow posts https://stackoverflow.com/questions/61257536/how-to-perform-sum-of-previous-cells-of-same-column-in-powerbi and https://stackoverflow.com/questions/60641059/dax-formula-referencing-itself/60656874#60656874 on closed-form implementations, we're still stuck, with all roads leading to the dreaded circular dependecy error. I was momentarily excited thinking that the new OFFSET() function would help, but the DAX engine wasn't fooled. Help me Greg_Deckler , you're my only hope!
Recursive measure challange in DAX. From Excel to Power BI
Hi all, I have a challange to move a report from Excel to Power BI due to the following measure. For cell C3 the formula is as follows "=IF(OR(COUNTIF(B3:B8;"<"&C2)=6;COUNTIF(B3:B8;">"&C2)=6);AVERAGE(B3:B8);C2)" For the next cells in "C" column it's the same formula. The only exception is cell "C2" where the formula is just "=AVERAGE(B2:B7)". So my main challange is with formula "=IF(OR(COUNTIF(B3:B8;"<"&C2)=6;COUNTIF(B3:B8;">"&C2)=6);AVERAGE(B3:B8);C2)" because in order to calculate value for "C3" I need to calculate what was calculated in "C2" thus I think it's a Recursive problem which Power BI is not especially good at. But perhaps there is some workaround to do this. I appreaciate any advises.
