Create columns

[saanah2019]

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]

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]

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]

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]

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

[saanah2019]

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.