cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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

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:

1 ACCEPTED SOLUTION
Microsoft Employee

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

and maybe use the AVERAGEX function for your moving average?

Proud to be a Datanaut!

3 REPLIES 3
Microsoft Employee

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

Microsoft Employee

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

and maybe use the AVERAGEX function for your moving average?

Proud to be a Datanaut!

New Member

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.