Forum Discussion
Average Interactions based on historical dates
Hello everyone,
I have data with Date, Day and Interactions.
The interactions are spread accross weeks and I am looking for a mesaure to caluclate average interactons based on day (Monday, Tuesday etc..) for the last 4 weeks. In the below table I have data from 06/26 (Monday) till 07/23(Sunday). I need a measure to automatically calculate Monday average based on last 4 mondays interactions. That is (100+104+122+104)/4 = 108. Likewise it has to clauclate last 4 weeks average for Tuesday, Wednesday ...Sunday. When the new week arrives, the calculation has to shift by 1 week to pick up last 4 weeks average. Any help is appreciated.
| Date | Day | Interactions | Desired_Output |
| 6/26/2023 | Monday | 100 | 108 |
| 6/27/2023 | Tuesday | 89 | 95 |
| 6/28/2023 | Wednesday | 63 | 79 |
| 6/29/2023 | Thursday | 72 | 72 |
| 6/30/2023 | Friday | 33 | 55 |
| 7/1/2023 | Saturday | 20 | 28 |
| 7/2/2023 | Sunday | 10 | 14 |
| 7/3/2023 | Monday | 104 | |
| 7/4/2023 | Tuesday | 93 | |
| 7/5/2023 | Wednesday | 88 | |
| 7/6/2023 | Thursday | 72 | |
| 7/7/2023 | Friday | 60 | |
| 7/8/2023 | Saturday | 30 | |
| 7/9/2023 | Sunday | 15 | |
| 7/10/2023 | Monday | 122 | |
| 7/11/2023 | Tuesday | 101 | |
| 7/12/2023 | Wednesday | 75 | |
| 7/13/2023 | Thursday | 63 | |
| 7/14/2023 | Friday | 55 | |
| 7/15/2023 | Saturday | 32 | |
| 7/16/2023 | Sunday | 12 | |
| 7/17/2023 | Monday | 104 | |
| 7/18/2023 | Tuesday | 98 | |
| 7/19/2023 | Wednesday | 90 | |
| 7/20/2023 | Thursday | 80 | |
| 7/21/2023 | Friday | 70 | |
| 7/22/2023 | Saturday | 30 | |
| 7/23/2023 | Sunday | 18 |
prasadhebbar315 , Create a date table, join with date of you table, have this columns in date Table
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
WeekDay = weekday([Date],2)
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW formatLast 4 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), calculate(sum('Table'[Interactions]))) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-3 && 'Date'[Week Rank]<=max('Date'[Week Rank]) && 'Date'[WeekDay] =max('Date'[WeekDay ] )))
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
3 Replies
prasadhebbar315 , Create a date table, join with date of you table, have this columns in date Table
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
WeekDay = weekday([Date],2)
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW formatLast 4 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), calculate(sum('Table'[Interactions]))) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-3 && 'Date'[Week Rank]<=max('Date'[Week Rank]) && 'Date'[WeekDay] =max('Date'[WeekDay ] )))
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8Hi amitchandak What is Week Start Date in this calculation? Is it adding row numbers based on first and last date?
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
Thanks , the solution has worked...
