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 )
Greg_Deckler's avatar
Greg_Deckler
Community Champion
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 )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

    final values

     

     

    • 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...