Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
@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.
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
93 | |
87 | |
35 | |
35 |
User | Count |
---|---|
154 | |
102 | |
82 | |
64 | |
54 |