Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Previous 3Mos average Forecasted

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...
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

az38's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      az38
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi Anonymous 

      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