Forum Discussion

charlkeet's avatar
charlkeet
New Member
7 years ago

include variable costs in portfolio backtest

Hi

 

I'm fairly inexperienced in using measures and ran into the following issue:

I am building a equity portfolio backtest dashboard, the backend is a list of dates and monthly returns (about 30 years).

 

I've created a calculated column to calculate the portfolio nav based on the monthly returns by using the following calc:

Fund_nav = (EXP(SUMX(FILTER('all data','all data'[Date] <= EARLIER('all data'[Date])),LN(1+ [Returns]))) * 100) -- This returns a compound return series.
 
I have created measures to rebase this fund nav start to 100 based on the date slicer
Index = DIVIDE(CALCULATE(SUMx('all data','all data'[Fund_nav]),FILTER(TimeLine, TimeLine[Date])),[firstval],BLANK()) * 100
 
I have now created a what-if scenario to include monthly costs to deduct from monthly returns. The issue is that EARLIER can't be used in a measure and I can't use the fund_nav calc as a measure.
 
Is there any way to subtract the monthly costs from the monthly returns and create a new rebased nav  measure based on the date slicer selection?
 
Need Help

2 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi  charlkeet

     

    EARLIER CAN be used in a measure. The issue here is that when your "Fund_nav" code is in a calculated column, you have the row context 1 from the table where "Fund_nav is place", as usual in calculated columns, and row context 2 from your FILTER( ) invocation.

    If you place that code in a measure, the only row context will be that stemming from FILTER( ) and thus, calling EARLIER( ) will yield an error (no nested row contexts in this case).        

     

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi charlkeet,

     I have now created a what-if scenario to include monthly costs to deduct from monthly returns. The issue is that EARLIER can't be used in a measure and I can't use the fund_nav calc as a measure.

    I should agree with AlB. Although we always use EARLIER in calculated column, but it also can be used in a measure. 

     

    You could have a reference of this blog which explain to us what the difference for using EARLIER in calculated column and measure, and it also show us how to use EARLIER in a measure with an example.

     

    Best  Regards,

    Cherry