Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Murali
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:

Moving_Averages.png

1 ACCEPTED SOLUTION
Phil_Seamark
Microsoft Employee
Microsoft Employee

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

 

and maybe use the AVERAGEX function for your moving average?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
v-ljerr-msft
Microsoft Employee
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.Smiley Happy

 

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

 

Regards

Phil_Seamark
Microsoft Employee
Microsoft Employee

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

 

and maybe use the AVERAGEX function for your moving average?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.