Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

90 Day Rolling Average - same FCT Table NO DimDate Table

Hi Experts

 

I have one table in my model no dimdate table - if i used a dimdate tablei would have a 1 to 1 relationship between FCT Date and DimDate[Date]

 

i am trying to work out the =CORREL(B2066:B2155, D2066:D2155) (excel) in power BI - which i have done... as a measure see below

FAME0/Palm 90 Day Rolling Correlation = 
VAR __nuX = CALCULATE(AVERAGE(BioDiesels[Name: EU.RP.DBIO.ARGUS.PA0009276.PROMPT]))
VAR __nuY = CALCULATE(AVERAGE(BioDiesels[Name: EU.RP.DBIO.ARGUS.PA0009273.PROMPT]))

VAR __Numerartor = SUMX(BioDiesels,([Name: EU.RP.DBIO.ARGUS.PA0009276.PROMPT]-__nuX)*([Name: EU.RP.DBIO.ARGUS.PA0009273.PROMPT]-__nuY))
VAR __Denominator = SQRT(SUMX(BioDiesels,([Name: EU.RP.DBIO.ARGUS.PA0009276.PROMPT]-__nuX)^2) * SUMX(BioDiesels,([Name: EU.RP.DBIO.ARGUS.PA0009273.PROMPT]-__nuY)^2))

Return
    DIVIDE(__Numerartor,__Denominator,0)

 

My issue is that i need the 90 Day Rolling Correlation based on the above measure - cannot work it out - no matter what i try. 

unless the above measure can re-written as a cacaluated column for 90 Day Rolling Correlation???? if this possible.

 

Need Help