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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
DAX_pbi_learner
Frequent Visitor

Creating Moving Averages in PowerBI for travel time across different routes

Hi,
I have a table with route_ids, travel time (in minutes), date(date the travel time is recorded for). I need 30 day rolling average, 7 day rolling average across routes. However, since the dataset has about 40+ unique routes the dates are repeated(for each route) and I'm not sure how to code this in DAX while accounting for each route. All the examples for rolling/moving averages show total sales and dates for one product or stock. Any advice on functions I can incorporate?
Thank You 

 

2 REPLIES 2
Greg_Deckler
Super User
Super User

@DAX_pbi_learner For general guidance: Better Rolling Average - Microsoft Power BI Community

 

If you can provide sample data and expected output, can be more specific. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

DAX_pbi_learner_0-1675718576209.png

Above is the sample data with: the route id, date of week, date, mmdd, start time, end time, and finally the travel time. Each route in the dataset has 3 records per day for 3 different time blocks (5am to 10am, 10am to 2pm, and 2pm to 7pm). My expected output (in this example, I've calculated 7 day Moving Average and 7 Day Weekday Moving Averages for one ID and one time slot) which I'm trying to do via DAX:

id/route

dow

date

mmdd

startTime

endTime

tt_mins

7 day moving average

7 day moving average weekday

39

Wednesday

1/1/2020

1-Jan

5am

10am

1.826

  

39

Thursday

1/2/2020

2-Jan

5am

10am

1.874

  

39

Friday

1/3/2020

3-Jan

5am

10am

1.848

  

39

Saturday

1/4/2020

4-Jan

5am

10am

1.814

  

39

Sunday

1/5/2020

5-Jan

5am

10am

1.893

  

39

Monday

1/6/2020

6-Jan

5am

10am

1.843

  

39

Tuesday

1/7/2020

7-Jan

5am

10am

1.861

9.142995335

 

39

Wednesday

1/8/2020

8-Jan

5am

10am

1.851

9.470765363

 

39

Thursday

1/9/2020

9-Jan

5am

10am

1.888

9.64475664

9.567319854

39

Friday

1/10/2020

10-Jan

5am

10am

1.837

9.8329354

10.01404385

39

Saturday

1/11/2020

11-Jan

5am

10am

1.836

9.924645453

 

39

Sunday

1/12/2020

12-Jan

5am

10am

1.821

9.813646952

 

39

Monday

1/13/2020

13-Jan

5am

10am

1.87

9.779827003

10.02897263

39

Tuesday

1/14/2020

14-Jan

5am

10am

1.891

9.786382285

10.22525433

@Greg_Deckler  let me know if this makes more sense. 

Thank You

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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