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 problem by having a table : MvgAvg which looks like this: It has a date column( always 1st of Month to indicate monthly sales) and Sales. I want to calculate the moving average for last 3 months. 

i.e for 1/12/2022 = Average sales of (Dec, Nov, Oct)  which is = (1 + 2 +3 )/ 3 = 2...

     for 1/11/2022 = Average sales of ( Nov, Oct,Sep) which is =  ( 2+3+4)/3 = 3 ..and so on..

 

I have created a measure [Total Sales]  = SUMX(MvgAvg,MvgAvg[Sales])

I have created this Measure Avg: 

Avg = CALCULATE(
AVERAGEX(
SUMMARIZE(
'MvgAvg',
'MvgAvg'[Month],
'MvgAvg'[Year]),
[Total Sales]),

DATESINPERIOD(MvgAvg[Date],LASTDATE(MvgAvg[Date]), -3, MONTH)

))

 

But, the result is same as the value in Column Sales instead of '3 month rolling average'.

Can you please help me understand what is the problem. 

 

Thanks in advance...

 

  • 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] )
    )

2 Replies

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