5 Day Moving average - No Weekends in Data and how I skip weekends from my moving averages?

[Murali]

My data has only weekday, as per given formulas, my moving averages are considering weekends (Sat and Sudays) also as dates. How I skip those in my moving averages? please help.

My formulas:

5dayMovingSum =

CALCULATE(sum(Query1[Inv_Amount]),DATESINPERIOD(Query1[Inv_Date],LASTDATE(Query1[Inv_Date]),-5,day))

5dayMovingSum_Avg = CALCULATE(sum(Query1[Inv_Amount]),DATESINPERIOD(Query1[Inv_Date],LASTDATE(Query1[Inv_Date]),-5,day))/5

My Data screen shot:

[v-ljerr-msft]

Hi @Murali,

Have you tried the solution provided by Phil_Seamark? As your data only contains weekday, it should work for 5 Day Moving Average in this scenario.

@Phil_Seamark Really brilliant solution! I was trying to add a "WeekDay" column to solve this which seems no necessary.

Regards

[Phil_Seamark · Accepted solution]

You could try LASTDATE(Query1[Inv_Date]),-7,day)

and maybe use the AVERAGEX function for your moving average?

[Murali]

@Phil_Seamark Thank you. This is a great workaround for my issue.