Forum Discussion
Accumulated yield bonds changing the date range analysis
Good morning everybody, I need to get the accumulated yield for each bond shown in the Portfolioid Column for for a date range. The start and end date of analysis is variable using a slicer.
The Accumulated Yield is based on the following rule:
Time Date Yield R (Acum Yield)
0 04/01/2020 0,06% R0 = 100
1 05/01/2020 0,12% R1 = R0 * (1 + Yield Time 1) - 1
2 06/01/2020 0,04% R2 = R1 * (1 + Yield Time 2) - 1
3 07/01/2020 0,30% R3 = R2 * (1 + Yield Time 3) - 1
4 08/01/2020 0,02% R4 = R3 * (1 + Yield Time 4) - 1
In another analysis the initial date could be 06/01/2020, and for this date Ro = 100 (always the first value must be 100).
Thanks in advance for the help
Hi Anonymous
Here is the solution with dynamic measure https://we.tl/t-DyjketthonYet there is a minor bug which I could not fix but hopfully will not cause any issue
Acum Yield = VAR CurrentIDTable = CALCULATETABLE ( Bonds, ALLEXCEPT ( Bonds, Bonds[PortfolioId] ) ) VAR FirstDateSelected = CALCULATE ( MIN ( Bonds[Date] ), ALLSELECTED ( Bonds[Date] ) ) VAR FirstSelectedYield = MAXX ( FILTER ( CurrentIDTable, Bonds[Date] = FirstDateSelected ), Bonds[Yield] ) VAR CurrentDate = SELECTEDVALUE ( Bonds[Date] ) VAR T1 = FILTER ( CurrentIDTable, Bonds[Date] <= CurrentDate && Bonds[Date] >= FirstDateSelected ) VAR T2 = ADDCOLUMNS ( T1, "@A", IF ( Bonds[Date] = FirstDateSelected, 1, Bonds[Yield] + 1 ) ) VAR Result = PRODUCTX ( T2, [@A] ) RETURN Result
14 Replies
- tamerj1
Community Champion
Hi Anonymous
Here is the solution with dynamic measure https://we.tl/t-DyjketthonYet there is a minor bug which I could not fix but hopfully will not cause any issue
Acum Yield = VAR CurrentIDTable = CALCULATETABLE ( Bonds, ALLEXCEPT ( Bonds, Bonds[PortfolioId] ) ) VAR FirstDateSelected = CALCULATE ( MIN ( Bonds[Date] ), ALLSELECTED ( Bonds[Date] ) ) VAR FirstSelectedYield = MAXX ( FILTER ( CurrentIDTable, Bonds[Date] = FirstDateSelected ), Bonds[Yield] ) VAR CurrentDate = SELECTEDVALUE ( Bonds[Date] ) VAR T1 = FILTER ( CurrentIDTable, Bonds[Date] <= CurrentDate && Bonds[Date] >= FirstDateSelected ) VAR T2 = ADDCOLUMNS ( T1, "@A", IF ( Bonds[Date] = FirstDateSelected, 1, Bonds[Yield] + 1 ) ) VAR Result = PRODUCTX ( T2, [@A] ) RETURN Result Hi, tamerj1 , I tried the solution and it works great, the only bug it´s giving me is with the last value of the RGBP01-NEG Bond. The accumulated of the last date should be 100.97, in the table it shows 100, however, if I make a line chart, the value is shown correctly. Thank you very much for the solution. I will continue working with the data, around 15 thousand rows.
- tamerj1
Anonymous
I woulder why despite the fact that the code removes all filters except the one from the name. I believe this has to do with Autoexist feature.
however, the first version has another bug. In case any Yield that equals to the first selected Yield will be converted to "1". The 2nd version eliminates this problem. - tamerj1
Anonymous
You are right this is the but that I was talking about. Iwill have a chance to work on it tomorrow. I'll let you know if I was able to fix it.
- tamerj1
Please refer to the updated code in the marked solution
- tamerj1
Hi Anonymous
wold you please just copy pate the cslues of the same in a reply so I don't have to type them manually? Or otherwise please provide a sample file. Download link would be a good option. Thank you
Hi, I´ve put the link to a file in Google Drive, I hope it works.
Thank you very much
https://docs.google.com/spreadsheets/d/1K2h5tt2oZHsEGNpGqMyZrO6i7FwpPI6PUHDL_Qo2EAk/edit?usp=sharing
- tamerj1
Hi Anonymous
You did not answer my question. Therefore, I proceeded and assumed this shall be for each protfolio seperately.Here is the sample file with the solution. https://we.tl/t-H4OhS1Eh5Q
The code is not long but for your information this is the most complex code I've ever written. I checked results and it complies to your requirement as per my undestanding.
A = VAR CurrentIDTable = CALCULATETABLE ( Bonds, ALLEXCEPT ( Bonds, Bonds[PortfolioId] ) ) VAR FirstDateEver = MINX ( CurrentIDTable, Bonds[Date] ) RETURN IF ( Bonds[Date] = FirstDateEver, 100, Bonds[Yield] + 1 )Modified Yield = VAR CurrentIDTable = CALCULATETABLE ( Bonds, ALLEXCEPT ( Bonds, Bonds[PortfolioId] ) ) VAR FirstDateEver = MINX ( CurrentIDTable, Bonds[Date] ) VAR SecondDateEver = MINX ( FILTER ( CurrentIDTable, Bonds[Date] > FirstDateEver ), Bonds[Date] ) VAR CurrentDate = Bonds[Date] VAR T1 = FILTER ( CurrentIDTable, Bonds[Date] <= CurrentDate ) VAR T2 = FILTER ( T1, Bonds[Date] > SecondDateEver ) VAR Value1 = PRODUCTX ( T1, Bonds[A] ) VAR Value2 = SUMX ( T2, PRODUCTX ( T2, Bonds[A] ) ) RETURN IF ( Bonds[A] = 100, 100, Value1 - Value2 - 1 )I really appreciate your help. Thank you.
This is the result I am tryng to get: https://docs.google.com/spreadsheets/d/1K2h5tt2oZHsEGNpGqMyZrO6i7FwpPI6PUHDL_Qo2EAk/edit?usp=sharing