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 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.

4 Replies

  • 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)