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.