cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Skip 3-Month Moving Average for first 2 months

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.

Nachiket
1 ACCEPTED SOLUTION
Super User

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.

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

2 REPLIES 2
Super User

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.

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

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.