Re: Create columns

saanah2019 · ‎01-09-2020

I have 2 columns that I need to do a calulation with:

Start and End. These are sprints that we use and are in the form:

Start End

2.31.0.0	2.31.0.0
2.31.0.0	2.31.0.0
2.31.0.0	2.31.0.0
2.31.0.0	2.31.0.0
2.31.0.0	2.32.0.0
2.31.0.0	2.32.0.0
2.31.0.0	2.32.0.0
2.31.0.0	2.32.0.0
2.31.0.0	2.32.0.0
2.31.0.0	2.32.0.0
2.31.0.0	2.32.0.0
2.31.1.0	2.32.0.0
2.31.1.0	2.32.0.0
2.31.1.0	2.32.0.0
2.31.1.0	2.32.0.0
2.31.1.0	2.32.0.0
2.32.0.0	2.33.0.0
2.32.0.0	2.33.0.0
2.32.0.0	2.33.0.0
2.32.0.0	2.33.0.0
2.32.0.0	2.33.0.0
2.33.1.0	2.33.1.0
2.33.1.0	2.33.1.0

Now I want to create columns from these so that each sprint that is between the start and end date also shows up.

Our Sprint starts with 2.31.0.0,2.31.1.0,2.31.2.0,2.31.3.0 then repeat

2.32.0.0,2.32.1.0,2.32.2.0,2.32.3.0 then repeat

2.33.0.0,2.33.1.0,2.33.2.0,2.33.3.0 ....

So for example in our data points:

Start End

2.31.1.0 2.32.0.0

I want to be able to create columns that will be 2.31.2.0,2.31.30

ex 2:

Start End

2.32.0.0 2.33.0.0

This will have additional columns 2.32.1.,2.32.2.0,2.32.3.0

How can I do this? Thank you. I need columns because I will be creating a table with the sprint as headers and will track my projects.

Icey · ‎01-14-2020

Hi @saanah2019 ,

Sorry to reply late.

All columns are text.

Try this:

Column 1 = 
VAR S2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[Start], 4 ), 2 ) )
VAR S3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[Start], 3 ), 1 ) )
VAR E2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[End], 4 ), 2 ) )
VAR E3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[End], 3 ), 1 ) ) 
RETURN
    SWITCH (
        TRUE (),
        'Table 1'[Start] = 'Table 1'[End], BLANK (),
        S2 < E2
            && S3 + 1 < 4, CONCATENATE (
            2,
            CONCATENATE (
                ".",
                CONCATENATE (
                    S2,
                    CONCATENATE ( ".", CONCATENATE ( S3 + 1, CONCATENATE ( ".", 0 ) ) )
                )
            )
        ),
        S2 < E2
            && S3 + 1 = 4, 
            CONCATENATE (
            2,
            CONCATENATE (
                ".",
                CONCATENATE (
                    S2 + 1,
                    CONCATENATE ( ".", CONCATENATE ( 0, CONCATENATE ( ".", 0 ) ) )
                )
            )
        )
    )

Column 2 = 
VAR S2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[Start], 4 ), 2 ) )
VAR S3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[Start], 3 ), 1 ) )
VAR E2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[End], 4 ), 2 ) )
VAR E3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[End], 3 ), 1 ) )
RETURN
    SWITCH (
        TRUE (),
        'Table 1'[Column 1]=BLANK(),BLANK(),
        'Table 1'[Column 1] = 'Table 1'[End], BLANK (),
        S2 < E2
            && S3 + 2 < 4, CONCATENATE (
            2,
            CONCATENATE (
                ".",
                CONCATENATE (
                    S2,
                    CONCATENATE ( ".", CONCATENATE ( S3 + 2, CONCATENATE ( ".", 0 ) ) )
                )
            )
        ),
        S2 < E2
            && S3 + 2 = 4, CONCATENATE (
            2,
            CONCATENATE (
                ".",
                CONCATENATE (
                    S2 + 1,
                    CONCATENATE ( ".", CONCATENATE ( 0, CONCATENATE ( ".", 0 ) ) )
                )
            )
        )
    )

Column 3 = 
VAR S2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[Start], 4 ), 2 ) )
VAR S3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[Start], 3 ), 1 ) )
VAR E2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[End], 4 ), 2 ) )
VAR E3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[End], 3 ), 1 ) )
RETURN
    SWITCH (
        TRUE (),
        'Table 1'[Column 2]=BLANK(),BLANK(),
        'Table 1'[Column 2] = 'Table 1'[End], BLANK (),
        S2 < E2
            && S3 + 3 < 4, CONCATENATE (
            2,
            CONCATENATE (
                ".",
                CONCATENATE (
                    S2,
                    CONCATENATE ( ".", CONCATENATE ( S3 + 3, CONCATENATE ( ".", 0 ) ) )
                )
            )
        ),
        S2 < E2
            && S3 + 3 = 4, CONCATENATE (
            2,
            CONCATENATE (
                ".",
                CONCATENATE (
                    S2 + 1,
                    CONCATENATE ( ".", CONCATENATE ( 0, CONCATENATE ( ".", 0 ) ) )
                )
            )
        )
    )

Column 4 = 
VAR S2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[Start], 4 ), 2 ) )
VAR S3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[Start], 3 ), 1 ) )
VAR E2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[End], 4 ), 2 ) )
VAR E3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[End], 3 ), 1 ) )
RETURN
    SWITCH (
        TRUE (),
        'Table 1'[Column 3]=BLANK(),BLANK(),
        'Table 1'[Column 3] = 'Table 1'[End], BLANK (),
        S2 < E2, CONCATENATE (
            2,
            CONCATENATE (
                ".",
                CONCATENATE (
                    S2 + 1,
                    CONCATENATE ( ".", CONCATENATE ( S3, CONCATENATE ( ".", 0 ) ) )
                )
            )
        )
    )

Column 5 = 
VAR S2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[Start], 4 ), 2 ) )
VAR S3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[Start], 3 ), 1 ) )
VAR E2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[End], 4 ), 2 ) )
VAR E3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[End], 3 ), 1 ) )
RETURN
    SWITCH (
        TRUE (),
        'Table 1'[Column 4]=BLANK(),BLANK(),
        'Table 1'[Column 4] = 'Table 1'[End], BLANK (),
        S2 < E2, CONCATENATE (
            2,
            CONCATENATE (
                ".",
                CONCATENATE (
                    S2 + 1,
                    CONCATENATE ( ".", CONCATENATE ( S3+1, CONCATENATE ( ".", 0 ) ) )
                )
            )
        )
    )

Best Regards,

Icey

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

Icey · ‎01-09-2020

Hi @saanah2019 ,

I don't know what the structure of the result you want is. From what I understand, I did the following:

1. Create columns.

Column 1 = 
VAR S2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[Start], 4 ), 2 ) )
VAR S3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[Start], 3 ), 1 ) )
VAR E2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[End], 4 ), 2 ) )
VAR E3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[End], 3 ), 1 ) )
RETURN
    SWITCH (
        TRUE (),
        'Table 1'[Start] = 'Table 1'[End], BLANK (),
        S2 < E2
            && S3 + 1 < 3, CONCATENATE (
            2,
            CONCATENATE (
                ".",
                CONCATENATE (
                    S2,
                    CONCATENATE ( ".", CONCATENATE ( S3 + 1, CONCATENATE ( ".", 0 ) ) )
                )
            )
        ),
        S2 < E2
            && S3 + 1 = 3, CONCATENATE (
            2,
            CONCATENATE (
                ".",
                CONCATENATE (
                    S2 + 1,
                    CONCATENATE ( ".", CONCATENATE ( 0, CONCATENATE ( ".", 0 ) ) )
                )
            )
        )
    )

Column 2 = 
VAR S2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[Start], 4 ), 2 ) )
VAR S3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[Start], 3 ), 1 ) )
VAR E2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[End], 4 ), 2 ) )
VAR E3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[End], 3 ), 1 ) )
RETURN
    SWITCH (
        TRUE (),
        'Table 1'[Column 1]=BLANK(),BLANK(),
        'Table 1'[Column 1] = 'Table 1'[End], BLANK (),
        S2 < E2
            && S3 + 2 < 3, CONCATENATE (
            2,
            CONCATENATE (
                ".",
                CONCATENATE (
                    S2,
                    CONCATENATE ( ".", CONCATENATE ( S3 + 2, CONCATENATE ( ".", 0 ) ) )
                )
            )
        ),
        S2 < E2
            && S3 + 2 = 3, CONCATENATE (
            2,
            CONCATENATE (
                ".",
                CONCATENATE (
                    S2 + 1,
                    CONCATENATE ( ".", CONCATENATE ( 0, CONCATENATE ( ".", 0 ) ) )
                )
            )
        )
    )

Column 3 = 
VAR S2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[Start], 4 ), 2 ) )
VAR S3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[Start], 3 ), 1 ) )
VAR E2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[End], 4 ), 2 ) )
VAR E3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[End], 3 ), 1 ) )
RETURN
    SWITCH (
        TRUE (),
        'Table 1'[Column 2]=BLANK(),BLANK(),
        'Table 1'[Column 2] = 'Table 1'[End], BLANK (),
        S2 < E2, CONCATENATE (
            2,
            CONCATENATE (
                ".",
                CONCATENATE (
                    S2 + 1,
                    CONCATENATE ( ".", CONCATENATE ( S3, CONCATENATE ( ".", 0 ) ) )
                )
            )
        )
    )

Column 4 = 
VAR S2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[Start], 4 ), 2 ) )
VAR S3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[Start], 3 ), 1 ) )
VAR E2 =
    VALUE ( RIGHT ( LEFT ( 'Table 1'[End], 4 ), 2 ) )
VAR E3 =
    VALUE ( LEFT ( RIGHT ( 'Table 1'[End], 3 ), 1 ) )
RETURN
    SWITCH (
        TRUE (),
        'Table 1'[Column 3]=BLANK(),BLANK(),
        'Table 1'[Column 3] = 'Table 1'[End], BLANK (),
        S2 < E2, CONCATENATE (
            2,
            CONCATENATE (
                ".",
                CONCATENATE (
                    S2 + 1,
                    CONCATENATE ( ".", CONCATENATE ( S3+1, CONCATENATE ( ".", 0 ) ) )
                )
            )
        )
    )

2. Create tables.

Table 1-1 = 
FILTER (
    UNION (
        VALUES ( 'Table 1'[Start] ),
        VALUES ( 'Table 1'[Column 1] ),
        VALUES ( 'Table 1'[Column 2] ),
        VALUES ( 'Table 1'[Column 3] ),
        VALUES ( 'Table 1'[End] )
    ),
    [Start] <> BLANK ()
)

Table 1-2 = VALUES ( 'Table 1-1'[Start] )

Please check if it is what you want.

Best Regards,

Icey

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

saanah2019 · ‎01-10-2020

Hi @Icey , thank you so much for your effort. Your work for the first part is exactly what I wanted. The only thing left is that for the columns, they all end in .2 but out our sprint goes to .3. So how can I do that so that the it goes 2.31.1,2.31.2,2.31.3 and 2.32.0...

saanah2019 · ‎01-10-2020

@Icey , also when I use your formula for column 1, i get the error 'Cannot convert value '.' of type Text tot type number'.

saanah2019 · ‎01-09-2020

Forget about creating new columns, rows are just fine.

Please help me find the calculation that will keep on subtracting the end date - start date and then adding .1 to the start date until the start date and end date are equal.

Create columns

Helpful resources

FabCon & SQLCon – Barcelona 2026

Data Days 2026

Power BI DataViz World Championships - June 2026

How to Get Your Question Answered Quickly

Get Fabric or SQL Certified for Free.

Create columns

Helpful resources

FabCon & SQLCon – Barcelona 2026

Data Days 2026

Power BI DataViz World Championships - June 2026

How to Get Your Question Answered Quickly