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