Calculated Column or Measure for grouping prioritized tasks into sprints

Question

I have task-related data ordered by Priority with corresponding Story Points that I would like to group into sprints. I can calculate the Cumulative Points. However, I'm struggling to create a measure (or even a calculated column) to forecast in which sprint the task will occur given a known Velocity.

Assume 'Velocity' = 10

enter image description here

My goal is to determine the forecast sprint as seen in the table image. Basically, the sum of story points for a sprint cannot exceed the velocity. Unfortunately, simply dividing the Cumulative Points by the Velocity and rounding doesn't work. In this case, the 11th task would fall into sprint 3, but the total story points for the sprint would exceed the velocity.

I've reviewed Greg Deckler's For/While solution, but I couldn't get it to work for this problem. I'm fairly new to DAX, but I did try a variety of other options but couldn't find a solution.

Answer 1

I would do this using Power Query / M language.

let
    Source = Table.FromColumns({
        {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13},
        {2, 5, 1, 3, 1, 3, 1, 7, 2, 1, 1, 5, 3}
    }, type table [Priority = Int32.Type, Story Point = Int32.Type]),
    Velocity = 10,
    AddedSprint = Table.FromRecords(
        List.Accumulate(
            Table.ToRecords(Table.Sort(Source, "Priority")),
            [TotalSP = 0, CurrentSprint = 1, Value = {}],
            (state as record, record as record) as record =>
                let sp = record[Story Point],
                    nextState =
                        if state[TotalSP] + sp > Velocity then [
                            TotalSP = sp,
                            CurrentSprint = state[CurrentSprint] + 1,
                            Value = state[Value] & {record & [Sprint = CurrentSprint]}
                        ]
                        else [
                            TotalSP = state[TotalSP] + sp,
                            CurrentSprint = state[CurrentSprint],
                            Value = state[Value] & {record & [Sprint = CurrentSprint]}
                        ]
                in nextState
        )[Value]
    )
in
    AddedSprint