Moving Average data not accurate there is non-working day

[hhooi]

Hi,
Need some advice. I am troubleshooting moving average issue can’t get it. I notice whenever there is non-working day, moving average shipment weight will be out. See below is the sample data and formula I am using. Highlighted in yellow is the record I found incorrect.

Appreciate your advice, thanks.

Ooi

[hhooi]

Hi,
Any advice on issue above?

Ooi

[Nick_M]

@hhooi
I’m obviously not Sam, but the reason you are seeing this is because DATESINPERIOD (and all time intell functions) will look all the dates in the range you feed it and will see blanks as zeros. There is a way to just ignore the blanks (instead of averaging in zeros, but it will still count as a day). So going off the assumption that you wanted a 5-day moving average of just the ship weight:

1. Need to create an index in the Shipping Table. This can easily be done in power query, but if can be added as a calculated column in DAX with the following:

=
Var CurrentRow= Table1[Ship Date]
Return

CALCULATE(
Countrows( Table1), Filter ( all ( Table1), CurrentRow >= Table1[Ship Date]))

Then write our averagex function using that index column for our start and end columns:

 5 Day MA Index:=IF ( 
MAX( Table1[Index] ) >=5,  //Checks to see if there is enough data for an average
CALCULATE(                                      //Need to Change the filter context
    AVERAGEX(                                   //Interested in an Average, but need to iterate 
        FILTER(                                 //Filter the Shippng Table based on..
            ALL ( Table1),                      //Ensure we have the entire table to get the start and end dates
                MAX( Table1[Index]) >= Table1[Index] //Max Index of the current row is >= Table index
                    && MAX( Table1[Index])-4 <= Table1[Index]) //Max -4 (since 5 day ma) <= Table Index
    ,[ShipWeight] //That gives us the table for averagex, just need the expression
    ) 
)
)  

and then final pivot table:

Excel File:
Moving Average non-consec dates.xlsx (263.8 KB)

-Nick