Average Interactions based on historical dates

[prasadhebbar315]

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

[amitchandak · Accepted solution]

@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 format

Last 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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[prasadhebbar315]

Thanks , the solution has worked...

[prasadhebbar315]

Hi @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)