Ok, here's the situation (my parents went away on a weeks vacation... 😁). I'm creating metrics for badging workers for an upcoming project where we're bringing almost 800 new workers over the course of about 8 weeks.

The Table1 has rows for each worker being in-processed/badged with their status, start and end date/times, sourced from a SharePoint Online list.

The Table2 (also from SharePoint) has a categorical date list with the # of people expected to in-process on each of those dates. I've added a calculated column for the running sum of the expected workers.

The Table3 just has the daily dates from the start of our in-processing run to the end (because I'm pretty darn new at this and I couldn't think of a better way to do get a list of continuous dates). I've got a calculated column (call this BadgedOnDate) on this table that counts all the rows from Table1 where the Complete Date matches the date on the row of Table3 (using the CountRows and RelatedTable functions) and then another calculated column to give me the running sum of those daily counts.

Have relationships from Table1 to Table3 on the Complete Date and Table2 to Table3 on the In-Process Date.

Here's the conundrum. On Table2, I need to calculate the running sum of the BadgedOnDate calculated column from Table3 from the In-Process Date for that row UP TO the NEXT In-Process date on the table.

E.g., for the row for 8/21/2023, I want to calculate the running sum of the BadgedOnDate values from 8/21/2023 up to (but not including) the next date in the table (which, in this case is 8/28/2023, but it's not always 7 days between in-processing dates).

I've tried googling this and while there were results that seemed like they MIGHT be hinting in this direction, I'm not savvy enough to adapt those solutions to work for this.

Hopefully I've explained this clearly enough to understand the situation and someone can help! 🤞

​