Forum Discussion

sonshine14916's avatar
sonshine14916
Regular Visitor
4 years ago
Solved

Need help wit DAX Averagex(rolling average)

Hi, I am not able to achieve the intended result with AverageX DAX expression. Can you please help to understand what is wrong with my AverageX DAX expression. Background: I have simplified the pr...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    I tried to create a sample pbix like below.

    I hope the below can help to provide some ideas on how to apply it to your data model.

     

     

    Value three months rolling avg: =
VAR newtable =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( Data, 'Calendar'[Month-Year] ),
            "@valuetotal", [Value total:]
        ),
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, MONTH )
    )
RETURN
    IF (
        HASONEVALUE ( 'Calendar'[Month-Year] ),
        AVERAGEX ( newtable, [@valuetotal] )
    )
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix like below.

I hope the below can help to provide some ideas on how to apply it to your data model.

 

 

Value three months rolling avg: =
VAR newtable =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( Data, 'Calendar'[Month-Year] ),
            "@valuetotal", [Value total:]
        ),
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, MONTH )
    )
RETURN
    IF (
        HASONEVALUE ( 'Calendar'[Month-Year] ),
        AVERAGEX ( newtable, [@valuetotal] )
    )