Calculate Last 4 Weeks SUM in calculated column in a daily record table

[mbahonen]

Hello,

I'm sure I've over-complicated this, but I'm a little stuck with something. I have a table with employee records. Each employee has daily records for the past year (so each week has 7 records per employee). I have a column 'DailyDetails'[Summary] that is either a 1 or 0 based on some other criteria, but only applies that on Day 1 of each week. 

I need to SUM that column 'DailyDetails'[Summary] for each employee going back 4 weeks at a time. I can do this easily enough in a measure, but I need to be able to filter on this value, so I am trying to create it as a calculated column, but I keep getting circular reference errors. 

Yes, I have a date table. 

Sample data below:

This sample is only for one Employee ID number (1), but imagine thousands of ID's with their own records on the same table. I need a new column (not measure) that calculates the last 4 weeks of the [Summary] column on the first day of the week for each employee ID, but that ignores the 5th week (so excluding WeekNum 39).

I'd expect the below total applied to Week 1 of each Week in WeekNum 43 = 4, but WeekNum 42 = 3

I may need to filter or group by one or two other columns as well (to exclude the row when it meets certain criteria).

Thanks in advance!

TableName = 'DailyDetails'

WeekNum	ID	Start of Week	Date	Day of Week	Summary
43	1	10/22/2023	10/28/2023	7	0
43	1	10/22/2023	10/27/2023	6	0
43	1	10/22/2023	10/26/2023	5	0
43	1	10/22/2023	10/25/2023	4	0
43	1	10/22/2023	10/24/2023	3	0
43	1	10/22/2023	10/23/2023	2	0
43	1	10/22/2023	10/22/2023	1	1
42	1	10/15/2023	10/21/2023	7	0
42	1	10/15/2023	10/20/2023	6	0
42	1	10/15/2023	10/19/2023	5	0
42	1	10/15/2023	10/18/2023	4	0
42	1	10/15/2023	10/17/2023	3	0
42	1	10/15/2023	10/16/2023	2	0
42	1	10/15/2023	10/15/2023	1	1
41	1	10/8/2023	10/28/2023	7	0
41	1	10/8/2023	10/27/2023	6	0
41	1	10/8/2023	10/26/2023	5	0
41	1	10/8/2023	10/25/2023	4	0
41	1	10/8/2023	10/24/2023	3	0
41	1	10/8/2023	10/23/2023	2	0
41	1	10/8/2023	10/22/2023	1	1
40	1	10/1/2023	10/7/2023	7	0
40	1	10/1/2023	10/6/2023	6	0
40	1	10/1/2023	10/5/2023	5	0
40	1	10/1/2023	10/4/2023	4	0
40	1	10/1/2023	10/3/2023	3	0
40	1	10/1/2023	10/2/2023	2	0
40	1	10/1/2023	10/1/2023	1	1
39	1	9/24/2023	9/30/2023	7	0
39	1	9/24/2023	9/29/2023	6	0
39	1	9/24/2023	9/28/2023	5	0
39	1	9/24/2023	9/27/2023	4	0
39	1	9/24/2023	9/26/2023	3	0
39	1	9/24/2023	9/25/2023	2	0
39	1	9/24/2023	9/24/2023	1	0

[lbendlin]

 going back 4 weeks

including the current day?  full weeks?  or 28 days?

I'd expect the below total applied to Week 1 of each Week in WeekNum 43 = 4, but WeekNum 42 = 3

what is "Week 1 of each Week" ?

[mbahonen]

Week is determined by the Start of Week Date which is a field in our data and how our data is joined to our Calendar table. Its the most important aspect of this model. I have it in my sample data for that reason. 

[lbendlin]

and how our data is joined to our Calendar table.

Usually Calendar tables are joining to the fact tables via the date column rather than the aggregate (start of week). 

[mbahonen]

I'm aware of that. I actually think I misspoke. It is joined via date. Its just not usually relevant for this report.