Moving Average

[Fitin1rb]

Hello

I am trying to create a daily moving average.  I have no problem creating the moving average DAX calculation, but issue comes more in the detail. 

Here is the background.  I am trying to calculate a daily moving average based on the number of deals booked per day with this formula

Moving Average Booked Deals = IF(ISBLANK( [Total Booked Deals] ),
BLANK(),
AVERAGEX(
   DATESINPERIOD(
     Calendar[Date],
   LASTDATE(Calendar[Date] ),
   -'Moving Average What If'[MA Days], DAY ),
   [Total Booked Deals] ) )

Now the issue I have is that there are days that do not have deals booked (6/3/17 6/4/17 etc below) so the moving average is not taking those days into account......but I want them too.

Is there a way to have the moving average include dates that have no data so that I can get a true moving average for the number of deals booked?

Thank You!!

Ryan

[v-chuncz-msft · Accepted solution]

@Fitin1rb,

It seems that you may add zero to the formula.

[Total Booked Deals] + 0

[Fitin1rb]

@v-chuncz-msft worked perfect, such a simple solution! Thanks for the advice!

[Greg_Deckler]

You will likely have to create a SUMMARIZE table and then calculate the AVERAGE off of that table. You could do this in a variable in your DAX formula for your measure.

[Fitin1rb]

Thanks for the quick response, I am new to using Variables in my functions can you provide me with an example of what my variable would look like in this situation?

Thanks!