Forum Discussion
Rolling 6 Week Average
I have a data set with date, agent names, task documents and hours. I calculate documents per hour by creating a measure Sum(Docs)/Sum(Hours). What I need is a 6 week average of docs per hour? So I need it to look back 6 weeks from the date and sum Docs and Hours so that I can plot both the Docs per Hour and the 6 week averge Docs per Hour on a line chart together. I also need this 6 week average to change dynamically with filters/slicers. For example, If I select both agents...it should be a combined 6 week average, or a specific task...it should only be a 6 week average for that task and that agent
| Date | Agent Name | Task | Docs | Hours |
| 3/2/2019 | Agent A | BALANCE | 147 | 43.8 |
| 3/2/2019 | Agent B | SM-BANK-APPL | 2 | 5.383333 |
| 4/6/2019 | Agent A | BALANCE | 115 | 38.166666 |
| 4/13/2019 | Agent B | BALANCE | 6 | 5.416666 |
| 3/9/2019 | Agent B | SM-BANK-APPL | 2 | 10.933333 |
| 5/4/2019 | Agent A | BALANCE | 123 | 41.583333 |
5/18/2019 | Agent A | BALANCE | 75 | 47.533333 |
| 5/11/2019 | Agent B | BALANCE | 5 | 5.25 |
| 4/27/2019 | Agent A | BALANCE | 98 | 45.1 |
HI Anonymous,
You can create a calculated column to calculate the 'rolling' average based on current date and agent, but it not able to be dynamic changes based on filter/slicer. Please use measure formula to instead, it can interact and respond with filter/slicers.
Time Intelligence "The Hard Way" (TITHW)
Regards,
Xiaoxin Sheng
Thanks everyone. I tried the solutions suggested, but none of them got me quite what I needed. What eneded up working was actually pretty simple. I had to create two measures. A rolling 6 Week Docs Total and a rolling 6 week Hours Total.
My calculation for Rolling 6 Week Docs Total:
Doc6Wk = CALCULATE(sum('Sample'[Docs]),DATESINPERIOD('Sample'[Date],LASTDATE('Sample'[Date]),-42, DAY),ALL('Sample'[Agent ID]))The calculation for Rolling 6 Week Hours was the same except replace "Docs" with "Hours". I added the "ALL" statement because I ended up plotting a line graph of the agent's Docs per Hour vs the entire group's Docs per Hour. Then I used both measures in a new measure.Group Rolling 6 Week Docs Per Hour calculation:Group Docs Per Hour = CALCULATE(DIVIDE([Doc6Wk],[Hrs6Wk]),ALL('Sample'[Agent ID]))It works perfectly. Thanks again for all of the help, it ended up leading me to what I needed.
6 Replies
Check this file. It has 12 weeks rolling using a calendar. Please check this diff would
Last 6 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-6 && 'Date'[Week Rank]<=max('Date'[Week Rank])))https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
That is rolling 42 days or 6 week.
Can you share sample data and sample output.
Thanks everyone. I tried the solutions suggested, but none of them got me quite what I needed. What eneded up working was actually pretty simple. I had to create two measures. A rolling 6 Week Docs Total and a rolling 6 week Hours Total.
My calculation for Rolling 6 Week Docs Total:
Doc6Wk = CALCULATE(sum('Sample'[Docs]),DATESINPERIOD('Sample'[Date],LASTDATE('Sample'[Date]),-42, DAY),ALL('Sample'[Agent ID]))The calculation for Rolling 6 Week Hours was the same except replace "Docs" with "Hours". I added the "ALL" statement because I ended up plotting a line graph of the agent's Docs per Hour vs the entire group's Docs per Hour. Then I used both measures in a new measure.Group Rolling 6 Week Docs Per Hour calculation:Group Docs Per Hour = CALCULATE(DIVIDE([Doc6Wk],[Hrs6Wk]),ALL('Sample'[Agent ID]))It works perfectly. Thanks again for all of the help, it ended up leading me to what I needed.
