Dynamic column calculation for return of trading strategy

[Innovare]

Hi all

We would like to visualize our trading strategy.  We have column 1 with a reference index (Dow Jones). The second column (column 2) contains a trading signal (1 for being invested in the Dow Jones; 0 for not being invested in the Dow Jones). In the third column (column 3), we would like to calculate the return of the trading strategy. We assume an initial investment of 100.

Is it possible to calculate column3 (Trading Strategy) directly in Power BI as a measure (DAX).

Example

Dow Jones	Trading Signal	Trading Strategy
20941		100.00
20981	1	100.19
20975	0	100.19
20996	1	100.30
20764	0	100.30
20548	1	99.25

In Excel we would use the following excel formula for calculating the trading strategy.

Excel Formula

100

=IF(B3=1;C2*A3/A2;C2)

=IF(B4=1;C3*A4/A3;C3)

=IF(B5=1;C4*A5/A4;C4)

=IF(B6=1;C5*A6/A5;C5)

=IF(B7=1;C6*A7/A6;C6)

Thanks for any help!

B&L

[v-sihou-msft]

@Innovare

In this scenario, you just need to add a column to get the previous Dow Jones indexes and calculate the rate. Then we can use Product() function to calculate the trending. I assume your Dow Jones indexes is daily, I use an index column in my sample.

1. Create a previous Dow Jones column.

Previous Dow Jones =
CALCULATE (
    SUM ( Table4[Dow Jones] ),
    FILTER ( Table4, Table4[Index] = EARLIER ( Table4[Index] ) - 1 )
)

2. Create a Rate column.

Rate =
IF (
    ISBLANK ( Table4[Previous Dow Jones] ),
    100,
    IF (
        Table4[Trading Signal] = 1,
        Table4[Dow Jones] / Table4[Previous Dow Jones],
        1
    )
)

3. Then create a Trending measure.

Trading Strategy =
CALCULATE (
    PRODUCTX ( Table4, Table4[Rate] ),
    FILTER ( ALL ( Table4 ), Table4[Index] <= MAX ( Table4[Index] ) )
)

Regards,

[Innovare]

Great support, Simon. Many thanks.

Just one follow-up question:is it possible to get that all done in a measure as we would like to make the strategy dynamic, meaning that if we chose a certain date, the calculation runs from this date and normalizes the first date at 100.

Many thanks

[v-sihou-msft · Accepted solution]

@Innovare

For this scenario, you need to build your Previous Dow Jones into a measure:

Previous Dow Jones Measure = 
Var CurrentIndex = CALCULATE( MAX( Table4[Index] ) )

return
CALCULATE( SUM( Table4[Dow Jones] ), FILTER( ALLSELECTED( Table4 ), Table4[Index] = CurrentIndex - 1 ))

Then create a Rate into a measure as well:

Rate Measure = IF([Previous Dow Jones Measure]=BLANK(),100,(IF(SUM(Table4[Trading Signal])=1,SUM(Table4[Dow Jones])/[Previous Dow Jones Measure],1))) 

Now you Trading Strategy should based on above Rate measure.

Trading Strategy Measure = CALCULATE(PRODUCTX(Table4,[Rate Measure]),FILTER(ALL(Table4),Table4[Index]<=MAX(Table4[Index])))

Regards, 

[Innovare]

Great support, Simon!

It worked out. However, we had to adjust the formula slighty:

Trading Strategy Measure = CALCULATE(PRODUCTX(Table4,[Rate Measure]),FILTER(ALLSELECTED(Table4),Table4[Index]<=MAX(Table4[Index])))

Instead of

Trading Strategy Measure = CALCULATE(PRODUCTX(Table4,[Rate Measure]),FILTER(ALL(Table4),Table4[Index]<=MAX(Table4[Index])))

Cheers

[Greg_Deckler]

I would not call that scenario a particularly strong use case for Power BI, that is much more an Excel problem. That being said, I would start with a running total calculation and then see if you could apply your on/off logic.

http://www.daxpatterns.com/cumulative-total/

Will try to noodle on a way to solve your problem.