Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Correlation Coefficient DAX Measure Error

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 ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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 )
visheshjain's avatar
visheshjain
Impactful Individual
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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