Moving average

[Anonymous]

This formula only counts the days in which sales happen.  How can I rewrite the dax formula to give me the 1 month moving average which includes all days, excluding weekends and holidays, into the average?

Example: in the last month, my sales where $500 every working day except 4 days which had zero sales.  I want my formula to return something under $500, because there were 4 days with no sales yet my formula below returns $500 because it does not include the days with no sales.

My date table has a column "WorkingDays", which has a 1 for weekdays and a 0 for weekends and holidays.

Sales 1M MA = AVERAGEX( DATESINPERIOD( DateDimension[Date], LASTDATE( DateDimension[Date]), -1, MONTH ), [Total Sales] )

[Anonymous · Accepted solution]

@Anonymous,

Please take a look at the following blog about how to calculate moving average without weekends/holidays.

http://sqlblog.com/blogs/alberto_ferrari/archive/2011/01/26/powerpivot-stocks-exchange-and-the-moving-average.aspx

Regards,
Lydia