Need help wit DAX Averagex(rolling average)

[sonshine14916]

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...

[sonshine14916]

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.

[Jihwan_Kim · Accepted solution]

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