I want to calculate the three-month average of the current month and the previous and next month.

[Eiji77]

I want to calculate the three-month average of the current month and the previous and next month.

How do I make a Dax?

I want to help.

[Mariusz]

Hi @Eiji77 

You can try something like below.

Measure = 
VAR _start = FIRSTDATE( DATEADD( 'Table'[Date], -1, MONTH ) )
VAR _end =  LASTDATE( DATEADD( 'Table'[Date], 1, MONTH ) ) 
RETURN 
    CALCULATE( 
        AVERAGE('Table'[Value]) , 
        DATESBETWEEN( 'Table'[Date], _start, _end )
    ) 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Eiji77]

Thank you @Mariusz ,

However, the results are now attached.

Do you know why this happened?

[Eiji77]

@Mariusz ,This is possible with a simple test model, but not with my model.

[Mariusz]

Hi @Eiji77 

Try something like below.

AVG = 
VAR _start = FIRSTDATE( DATEADD( 'Table'[Date], -1, MONTH ) )
VAR _end =  LASTDATE( DATEADD( 'Table'[Date], 1, MONTH ) ) 
VAR _tbl = 
CALCULATETABLE( 
    'Table',
    ALL( 'Table' ),
    DATESBETWEEN( 'Table'[Date], _start, _end ) 
) 
VAR _months = 
GROUPBY(
    _tbl,
    'Table'[Year Month] ,
    "sum", SUMX( CURRENTGROUP(), 'Table'[Value] ) 
)
RETURN 
AVERAGEX( _months, [sum] )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[dax · Accepted solution]

Hi Eiji77,

Below is my design:

month	amount
7/1/2017	100
8/1/2017	200
9/1/2017	100
10/1/2017	300
7/1/2018	100
8/1/2018	500
9/1/2018	150
10/1/2018	250
7/1/2019	200
8/1/2019	300
9/1/2019	100
10/1/2019	200

create a measure like below

Measure 6 =
CALCULATE (
    AVERAGE ( 'avg'[amount] ),
    FILTER (
        ALL ( 'avg' ),
        YEAR ( 'avg'[month] ) = YEAR ( MIN ( 'avg'[month] ) )
            && MONTH ( 'avg'[month] )
                >= MONTH ( MIN ( 'avg'[month] ) ) - 1
            && MONTH ( 'avg'[month] )
                <= MONTH ( MIN ( 'avg'[month] ) ) + 1
    )
)

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.