Extended Date Table - Add Agile Sprint schedule

[Melissa]

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:

1. AddToTable, is the table these columns should be added to
2. CalendarStartDate, is the first date from the calendar
3. CalendarEndDate, is the last date from the calendar
4. SprintDurationDays, for example, a duration of 2 weeks? enter 14
5. SprintStartDayOfWeek, weekday a sprint cycle starts, enter 0 = Sun up to 6 = Sat
   This is optional, when omitted the SprintStartDayOfWeek is Monday
6. 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.