Skip 3-Month Moving Average for first 2 months

[Anonymous]

Hi everyone, I'm fairly new to DAX so I could use all the help I can get here. I found the DAX definition for Moving/Rolling Average as below :

Rolling Average =

VAR LastDate_ =

LASTDATE ( Query1[date] )

RETURN

CALCULATE (

AVERAGEX ( VALUES ( 'Calendar'[MonthInCalendar] ), CALCULATE ( SUM ( Query1[OI_Prediction_Month] ) ) ),

FILTER(

ALL(Query1),

Query1[date] <= LastDate_

&& Query1[date] > DATEADD ( LastDate_, -3, MONTH )

)

)

The values I am getting are correct. However, I do not want the values to show up for the first 2 months as this is a

3-month rolling value. Right now, it just shows me the average for those particular month i.e. Month 1 & 2 on the x-axis.

How can I compute only the values starting from month 3 ?

Any help is appreciated.

Thanks in advance,

Nachiket

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

please try the below measure.

Rolling Average =
VAR LastDate_ =
LASTDATE ( Query1[date] )
RETURN
IF (
ISBLANK (
CALCULATE (
SUM ( Query1[OI_Prediction_Month] ),
DATEADD ( Query1[date], -3, MONTH )
)
),
BLANK (),
CALCULATE (
AVERAGEX (
VALUES ( 'Calendar'[MonthInCalendar] ),
CALCULATE ( SUM ( Query1[OI_Prediction_Month] ) )
),
FILTER (
ALL ( Query1 ),
Query1[date] <= LastDate_
&& Query1[date] > DATEADD ( LastDate_, -3, MONTH )
)
)
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Anonymous]

Hi Jihwan, much thanks. 😊 Once I read you DAX code, it came to me that the solution's quite simple. Guess I need more practice with DAX to catch up.