Forum Discussion
Create Sprint Table or Column with Dax
- 3 years ago
This is what I ended up going with.
My Sprints start on a Tuesday and are 2 week Sprints with 26 Sprints a year.
let StartDate = #date(2018, 1, 9), EndDate = Date.AddYears(Date.EndOfYear(DateTime.Date(DateTime.FixedLocalNow())),3), CurrentDate = DateTime.Date(DateTime.FixedLocalNow()), FirstSprint = 1, MaxSprint = 26, #"==SET PARAMETERS ABOVE==" = 1, #"==Build Date Column==" = #"==SET PARAMETERS ABOVE==", ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)), #"Converted to Table" = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}), #"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Columns as Date",{{"Date", type date}}), #"Added Days" = Table.AddColumn(#"Changed Type to Date", "Days", each [Date] - StartDate), #"Changed Type" = Table.TransformColumnTypes(#"Added Days",{{"Days", Int64.Type}}), #"Added SprintFromStart" = Table.AddColumn(#"Changed Type", "SprintFromStart", each Number.IntegerDivide([Days],14) + FirstSprint), #"Added Sprint" = Table.AddColumn(#"Added SprintFromStart", "Sprint", each if Number.IntegerDivide ( [SprintFromStart] , ( MaxSprint + 1) ) > 0 then [SprintFromStart] - ( MaxSprint * (( Number.RoundUp ( ( Number.IntegerDivide( [Days] , 14 ) + FirstSprint) / MaxSprint)) - 1)) else Number.IntegerDivide( [Days],14 ) + FirstSprint), #"Changed Type1" = Table.TransformColumnTypes(#"Added Sprint",{{"SprintFromStart", Int64.Type}, {"Sprint", Int64.Type}}), #"Added SprintName" = Table.AddColumn(#"Changed Type1", "SprintName", each if ([Sprint] = MaxSprint and Date.Month ([Date]) = 1) then Date.ToText(Date.AddYears([Date], -1), [Format="yyyy"])&"-"&Number.ToText([Sprint]) else Date.ToText([Date], [Format="yyyy"])&"-"&Number.ToText([Sprint])), #"Changed Type2" = Table.TransformColumnTypes(#"Added SprintName",{{"SprintName", type text}}), #"Added Sort" = Table.AddColumn(#"Changed Type2", "Sort", each if ([Sprint] = MaxSprint and Date.Month ([Date]) = 1) then Date.ToText(Date.AddYears([Date], -1), [Format="yyyy"])&Number.ToText([Sprint], "00")&Date.ToText([Date], [Format="dd"]) else Date.ToText([Date], [Format="yyyy"])&Number.ToText([Sprint], "00")&Date.ToText([Date], [Format="dd"])), #"Changed Type3" = Table.TransformColumnTypes(#"Added Sort",{{"Sort", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"Days", "SprintFromStart"}) in #"Removed Columns"Not perfect, but works for me.
Let me ask it another way: What determines if any one particular date is "in the current spring"? Do you have a sprint schedule that tells you when each starts? You must have some input to make that determination. And please don't tell me it is because the last sprint ended on X date, becasue I will just ask you, "what determines THAT?"
It is purely a static "Start Date" for the first Sprint 1 (of the year of the Start Date) that all the rest would be based on (resetting to Sprint 1 after 26 times each year), like entering the Start Date and End Date when using the CALENDAR() function. So based off a static Start and static Candence in the formula, all future dates would be identified to be in specific Sprints.