In the past month, I had only 20 working days, due to holidays and weekends in which we are closed.
I had sales totaling $1,000 last month.
Of the 20 days, 10 of the days I had no sales. The remaining 10 days, I had sales of $100 exactly for each of the 10 days. The dax formula below will return $100, because it is only looking at days in which sales occurred. I want the result to return $50. $1,000 sales / 20 days = $50.
If the first day I sell $100 and the second day I don’t have a sale, my moving average over the past two days should be $50. Right?
Sales 1M MA = AVERAGEX( DATESINPERIOD( DateDimension[Date], LASTDATE( DateDimension[Date]), -1, MONTH ), [Total Sales] )