Accumulated yield bonds changing the date range analysis

[Anonymous]

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

[Anonymous]

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

[Anonymous]

I think the bug (or problem) with the last value of the RGBP01-NEG bond, that places it as 100% is because the currency appears as GP and not as GBP, so the dynamic measure treats it as if it were a first value (100%). Thank you again for your solution. It´s very helpfull

[tamerj1 · Accepted solution]

Hi @Anonymous 
Here is the solution with dynamic measure https://we.tl/t-Dyjketthon

Yet 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

[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
    )

[Anonymous]

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 

[Anonymous]

This is a sample of data

[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

[Anonymous]

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]

@Anonymous 

Please confirm the calculation shall be done for each potfolioId separately. 

[Anonymous]

Sorry for the delay. Yes, the calculation shall be done for each potfolioId separately. Thank you

[tamerj1]

@Anonymous 

Did you check my solution?