Create Sprint Table or Column with Dax

[Juliecal73]

Hi,

I'm looking to create a dynamic Sprint table with DAX like a calendar table, with a start date for the first Sprint and a 2 week cadence for each subsequent Sprint, resetting each year. e.g. Current start date for Sprint 1 of 2019 is 1/8/2019 (since previous Sprint was Sprint 26 from 2018). Does anyone have the DAX formula for something like this?

My table is currently a static table pulled from XLS

Thanks

[Juliecal73 · Accepted solution]

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.

[ToddChitt]

Generally speaking, I don't ever limit my DATE dimension tables that much. Who cares if you have extra dates in there that are not referenced by fact rows? Such dates simply won't show up on visuals until there is matching fact data.

So to your question, can you put in dynamic logic in a column to determine if the date is within the specified sprint? Not sure what the logic or criteria would be. Can you be more specific?

[Juliecal73]

Hi,

Yes, I definitly considered adding a column to my Date Table too, and am happy to do that, and that is exactly the question I still have, which is what the formula would be for that. Identifying if a date is in a current Sprint wold still need some kind of specification on what identifies the current Sprint, and what number it is.

[ToddChitt]

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?"

[Juliecal73]

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.