Skip to main content
cancel
Showing results for 
Search instead 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

Reply
theDarkPrince
Advocate II
Advocate II

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.
 
Thanks in advance,
Nachiket
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @theDarkPrince 

 

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.

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.


Click here and Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi, @theDarkPrince 

 

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.

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.


Click here and Go to My LinkedIn Page


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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.