Correlation Coefficient DAX Measure Error

[Anonymous]

Hi Experts

See sample file

https://www.dropbox.com/s/40lecde6kv4fvud/BioDieselv1.1%20%281%29.pbix?dl=0 

Kindly refer to the last four column in the table. I am trying to work out the 90 day rolling based on  The Correlation Coefficient measure should start on the 10 may 2020 as that is data point 90 which is also the index value.

the expected end results are shown below - i cannot work out what working with my measure and why the expected end results are all wrong.

I took the measure from the Microsoft Power BI DAX Site to work out the Correlation Coefficent

The equalivant formula in excel is Correl(column A, column b) the range.

[Greg_Deckler · Accepted solution]

@Anonymous Is this what you are looking for? See PBIX attached below signature.

90 Days Rolling Average 2012-2023 2 = 
    VAR NumOfDays = 90
    VAR LastCurrentDate =
        MAX ( 'DimDate'[Date] )
    VAR Period =
        DATESINPERIOD ( 'DimDate'[Date], LastCurrentDate, - NumOfDays, DAY )
    VAR Result =
        AVERAGEX(FILTER('BioSales', [Date] <= LastCurrentDate && [Date] >= LastCurrentDate - 90),[2012-2023 Average])
    VAR FirstDateInPeriod = MINX ( Period, 'DimDate'[Date] )
    VAR LastDateWithSales = MAX ( 'BioSales'[Date] )
    RETURN
        IF ( FirstDateInPeriod <= LastDateWithSales, Result )
            

[Anonymous]

final values

[visheshjain]

Hi @Anonymous & @Greg_Deckler,

Spitballing here!

Could it be that the numbers are different from the desired output, as there are dates where the correlation is blank.

Since we are calculating the dates - 90 days in the date table, even the days where the correlation is blank, are being considered.

On the other hand the desired output is ignoring the days, where the correlation is blank and hence the difference in numbers.

Hope this helps.

Thank you,

Vishesh Jain

[Anonymous]

The excel formula is correl(a2:90,b2:b90) rolling forward...

[Anonymous]

Hi Greg, the values in measure __Roll90D are correct until we reach 02 April 2012 then after that point the numbers (whats expected goes out of sink with the excel check) .. expected results are in the image body of question. i need to hit those number as shown for May and Jun 2012

__Roll90D2 to also close but again goes out of sink 02 April 2012. 

[Anonymous]

The gods have spoken...let me check and feedback. Sir.