Moving Average issue - Workdays Only

DAX DAX Calculations
1

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] )
2

To me it looks like the issue is more that you are looking over every single day in the last month including weekends with this formula.

It doesn’t look like it is taking into account that you only want to average up working days.

Can you try something like this.

I want to see if this will give you the monthly moving average but only for working days.

Sales MA 1M Weekdays = 
VAR WorkingDays = FILTER(
                    CALCULATETABLE( Dates,
                         DATESINPERIOD( Dates[Date], LASTDATE( Dates[Date] ), -1, MONTH ) ),
                                 Dates[Day Type] = "Workday" )

RETURN
AVERAGEX(
    WorkingDays,
        [Total Sales] )

I think it does based on the data I’m working with but I want to check with your example first.

Chrs

1 Like
3

That returned the exact same results as my original formula until I added this the “+ 0” to the last part of your formula. I added the “+ 0” to my original formula and it was not correct but it worked perfectly with your formula that looks at only working days. Thank you so much. I guess adding the + 0 tells the formula to not just look at days which had sales, but to also look at days that was 0. I stumbled across the +0 on another post on the internet.

Sales MA 1M Weekdays =
VAR WorkingDays = FILTER(
CALCULATETABLE( Dates,
DATESINPERIOD( Dates[Date], LASTDATE( Dates[Date] ), -1, MONTH ) ),
Dates[Day Type] = “Workday” )

RETURN
AVERAGEX(
WorkingDays,
[Total Sales] + 0 )

4

Very interesting actually.

Like it a lot.

Nice one.