include variable costs in portfolio backtest

[charlkeet]

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?

[v-piga-msft]

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

[AlB]

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