Calculating moving average in column (not measure)...

wlknsn · ‎08-13-2018

Hi,

I currently have the following measure to calculate 2 moving averages:
- one on all gains

- another on all losses.

The averages are moving over the last 14 periodes within a timeframe (timestamps are ranked per timeframe), and timeframes can be hours, days, ... yet always having a respective timeframe. The following measure works, but I believe given it's pretty static, it should be calculated in a column instead of measure. Is this possible?

RSI = 

VAR 
	GainCalc = abs(CALCULATE (
    AVERAGEx(AllCandleData, [Gain]),
    filter(ALLEXCEPT ( AllCandleData,AllCandleData[Symbol],AllCandleData[Exchange],AllCandleData[Interval] ),
    AllCandleData[Rank] > (MAX(AllCandleData[Rank]) - 14 )
        && AllCandleData[Rank] <= MAX(AllCandleData[Rank])
)
    )
)
VAR 
	LossCalc = abs(CALCULATE (
    AVERAGEx(AllCandleData,[Loss]),
    filter(ALLEXCEPT ( AllCandleData,AllCandleData[Symbol],AllCandleData[Exchange],AllCandleData[Interval] ),
    AllCandleData[Rank] > (MAX(AllCandleData[Rank]) - 14 )
        && AllCandleData[Rank] <= MAX(AllCandleData[Rank])
)
    )
)

RETURN

if(LossCalc=0 , 100 , 100-(100/(1+DIVIDE(
	GainCalc,LossCalc,BLANK()))))

Unfortunately, the result should be repeated on all timeframes. Similar to last non blank. Check below for screenshot. Can anyone help me out making this work on a measure, or convert this into a calculated column?

wlknsn · ‎08-13-2018

I don't mind making a measure or calculated column by timeframe ...

Anonymous · ‎08-14-2018

HI @wlknsn,

Can you please share some sample data for test?

Regards,

Xiaoxin Sheng

wlknsn · ‎08-14-2018

Of course, link to some sample data where the RSI is calculated using the measure mentionned above. Again, if we can have this is a calculated column, performance would probably seriously enhanced, but not a must.

https://wetransfer.com/downloads/7f59fcbf232bd6e98cc53c1ad19c28fa20180814123130/73362c11c9e8ea78b551...

Detail

Summary