Help: Value in Column 1 = X, add Y to column 2

[bbajuscak]

Need assistance in Power BI desktop

I have 1 column, Sprint Team

Column is formatted in the following manner: Team1 25.Q3.S1

I need to create a second column to "translate" this value into a date. So, Team1 25.Q3.S1 = Team 1 2025 Quarter 3 sprint 1 = Team1 Oct. 1 2025.

How do I write the expression to populate column 2 with "Oct. 1, 2025"?

In column 1 I have numerous Teams, years, quarters and sprints.: Team1 25.Q3.S1, Team2 25.Q2.S2, Team3 25.Q3.S6 etc

I'd like to focus on the 25.Q3.S1 component to populate dates to associate with these sprints

I figure I'll need multiple if statments to map all in column 1 to column 2 dates

I am trying to build a simple table with 2 total columns, column 1 "Sprint" column 2 "Date".

Any insight is greatly appreciated

[bbajuscak]

I have been trying to make the folowing work...

Date = If(Contains([Sprint], "24.Q3.S1"), "Oct 1, 2024", [Date])

not quite working though

Current table only has the one column, "Sprint", trying to create the new column "Date"

[Anonymous]

Hi @bbajuscak ,

Thanks for rsbin's reply!
And I agree with @Ashish_Mathur , you are missing a comma at the end of each line:

And you can also try this DAX to create a calculated column, I don't know what effect your S1 (sprint 1) has on the month, so in DAX I chose to fix the month directly according to the quarter like rsbin, and did not use Sprint:

Date = 
VAR SprintText = RIGHT('Table'[Sprint Team], LEN('Table'[Sprint Team]) - FIND(" ", 'Table'[Sprint Team]))
VAR YearPart = "20" & LEFT(SprintText, 2)
VAR QuarterPart = MID(SprintText, 5, 1)
VAR SprintPart = MID(SprintText, 8, 1)

VAR YearNumber = VALUE(YearPart)
VAR QuarterNumber = VALUE(QuarterPart)
VAR SprintNumber = VALUE(SprintPart)

VAR QuarterStartMonth = SWITCH(
    QuarterNumber,
    1, 4,
    2, 7,
    3, 10,
    4, 1
)

VAR SprintStartDate = DATE(YearNumber, QuarterStartMonth, 1)

RETURN FORMAT(SprintStartDate, "mmm d, yyyy")

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rsbin]

@bbajuscak ,

There are a variety of Text Functions you can use.  You can create the following Calculated Columns:

Team = LEFT( [YourColumn], 5 )   // This will extract the Team number into its own column

Year = "20" & MID( [YourColumn], 7,2)  //  This should extract the two digit year, append it to 20 to provide 4 digit year
Quarter = MID( [ YourColumn], 10, 2)  //  This will extract the "Q3" portion from your text string.
Sprint = RIGHT( [YourColumn], 2)    // This will extract the Sprint Number, the last 2 characters of your string.

Date = SWITCH(
                     TRUE(),
                      [Quarter] = "Q1", "Mar1" & [Year],
                      [Quarter] = "Q2"  "Jun 1" & [Year],

                      [Quarter] = "Q3", "Oct 1" & [Year],

                       [Quarter] = "Q4", "Dec 1" & [Year]      // I think I have your Quarter Start dates correct.

You can simplify the number of columns.  I laid it out this way to make it easier to understand.

Hope this gets you going.

Regards,

Edited: SWITCH Formula to include commas per Ashish.  My bad, as I didn't test before answering.                   
    
     

[bbajuscak]

@rsbin Thanks for the input. I have tried this out and successfully get the Team, Year, Quarter and Sprint Number columns to populate.

But when I try to get the Date column, it get an error

[Ashish_Mathur]

Hi,

Before every [quarter], there should be a comma