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

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.