Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Juliecal73
Helper III
Helper III

Create Sprint Table or Column with Dax

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

Juliecal73_1-1661525043836.png

Thanks

1 ACCEPTED SOLUTION
Juliecal73
Helper III
Helper III

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.

View solution in original post

5 REPLIES 5
Juliecal73
Helper III
Helper III

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
Super User
Super User

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?




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





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.

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




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





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.

Juliecal73_0-1661526392347.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.