Previous 3Mos average Forecasted

[JWick1969]

Hi All,

I'm trying to create a measure to get Previous 3Mos average, yet could not figure out on how to do this in DAX. I need to get the previous 3 months average of completed pct infront of the current month dynamically.  Thank you.

Sample : previous 3 month average

1/1/2019 shows no average result

2/1/2019 shows no average result

3/1/2019 shows no average result

4/1/2019 shows average of completd pct of (Jan-Feb-Mar)

5/1/2019 shows average of completd pct of (Feb-Mar-Apr)

and so on

Sample Data and expected result:

Thank you. in Advance.

[JWick1969]

Is there anyone has an idea to do this in DAX? Thank you.

[az38]

Hi @JWick1969 

try a measure

Measure = 
var _AVGpct = CALCULATE(AVERAGE(Query[Completed pct]);FILTER(ALL(Query);datediff(Query[Month];SELECTEDVALUE(Query[Month]);MONTH)<=3 && Query[Month]< SELECTEDVALUE(Query[Month])))
var _AVGcount = CALCULATE(COUNTROWS(Query);FILTER(ALL(Query);datediff(Query[Month];SELECTEDVALUE(Query[Month]);MONTH)<=3 && Query[Month]< SELECTEDVALUE(Query[Month])))
RETURN
IF(_AVGcount>=3;_AVGpct;BLANK())

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[JWick1969]

Thanks for the reply.  I got an error which i think the semicolon. what is the use of semicolon? is this a parameter separator? by the way, my PBI version (April2019)

[az38]

Hi @JWick1969 

it's a system localization question.

replace all ";" in my statement to ","

do not hesitate to give a kudo to useful posts and mark solutions as solution