Hi @ClarkeM,
Give this a go. It’s a Custom Function that can be invoked on any Date table. As per your description this will add a Sprint Schedule for the entire table.
Parameters and how to use them:
-
AddToTable, is the table these columns should be added to
-
CalendarStartDate, is the first date from the calendar
-
CalendarEndDate, is the last date from the calendar
-
SprintDurationDays, for example, a duration of 2 weeks? enter 14
-
SprintStartDayOfWeek, weekday a sprint cycle starts, enter 0 = Sun up to 6 = Sat
This is optional, when omitted the SprintStartDayOfWeek is Monday
-
SprintStartNumber, integer to be assigned to the first added sprint cycle
This is optional when omitted the SprintStartNumber is 1
.
Here’s the function:
(AddToTable as table, CalendarStartDate as date, CalendarEndDate as date, SprintDurationDays as number, optional SprintStartDayOfWeek as number, optional SprintStartNumber as number ) as table =>
let
// //variables
// CalendarStartDate = #date( 2023, 1, 1),
// CalendarEndDate = #date( 2023, 3, 31),
// SprintDurationDays = 14,
// SprintStartDayOfWeek = 2, //optional default Monday =1, choose from 0 = Sun to 6 = Sat
// SprintStartNumber = 1, //optional default 1
//setup
SprintBaseLen = 7, //fixed to force weeks
sLen = Number.RoundUp( SprintDurationDays / SprintBaseLen, 0 ) * SprintBaseLen,
nSkip = List.PositionOf( List.Transform( List.Dates( CalendarStartDate, 7, Duration.From(1)), each Date.DayOfWeek( _, SprintStartDayOfWeek?? 1 ) +1 ), 1, 0 ),
Reps = Number.RoundDown( (Number.From( CalendarEndDate - CalendarStartDate ) - nSkip) / sLen, 0 ),
//logic
Result = Table.TransformColumnTypes( Table.Combine(
List.Generate(
()=> [
r = 1,
s = SprintStartNumber?? 1,
t = Table.FromColumns(
{
List.Repeat( {null}, nSkip ) & List.Repeat( {s}, SprintDurationDays ) & List.Repeat( {null}, sLen - SprintDurationDays ),
List.Repeat( {null}, nSkip ) & List.Repeat( {Date.AddDays( CalendarStartDate, nSkip)}, SprintDurationDays ) & List.Repeat( {null}, sLen - SprintDurationDays ),
List.Repeat( {null}, nSkip ) & List.Repeat( {Date.AddDays( CalendarStartDate, (nSkip + (SprintDurationDays * r)) -1)}, SprintDurationDays ) & List.Repeat( {null}, sLen - SprintDurationDays )
}, { "Sprint Number", "Sprint Start Date", "Sprint End Date"}
)
],
each [r] <= Reps,
each [
r = [r] + 1,
s = [s] + 1,
t = Table.FromColumns(
{
List.Repeat( {s}, SprintDurationDays ) & List.Repeat( {null}, sLen - SprintDurationDays ),
List.Repeat( {Date.AddDays( CalendarStartDate, nSkip + (sLen * (r-1)))}, SprintDurationDays ) & List.Repeat( {null}, sLen - SprintDurationDays ),
List.Repeat( {Date.AddDays( CalendarStartDate, nSkip + (sLen * (r-1)) + SprintDurationDays -1 )}, SprintDurationDays ) & List.Repeat( {null}, sLen - SprintDurationDays )
}, { "Sprint Number", "Sprint Start Date", "Sprint End Date"}
)
],
each [t]
)), {{"Sprint Number", Int64.Type}, {"Sprint Start Date", type date}, {"Sprint End Date", type date}}
),
Final = Table.FromColumns(
Table.ToColumns( AddToTable ) & Table.ToColumns( Result ),
Table.ColumnNames( AddToTable ) & Table.ColumnNames( Result )
)
in
Final
.
With this result
Amend to your needs.
All the best.
