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] )
    )
sonshine14916's avatar
sonshine14916
Regular Visitor
4 years ago

Hi Jihwan_win,

thank you for your quick confirmation and it worked fine.

The difference I noticed is that logic works if the  Calendar(date) table relationship is existing.

Summarise with the MvgAvg date somehow doesn't return it.

I am trying to also see if it works with CALCULATE, AVERAGEX, SUMMARIZE, DATESINPERIOD. But thanks for finding the solution. 

 

Thanks.