Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
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" ?
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.
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).
I'm aware of that. I actually think I misspoke. It is joined via date. Its just not usually relevant for this report.