Forum Discussion

saanah2019's avatar
saanah2019
Helper II
6 years ago

Create columns

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.02.31.0.0
2.31.0.02.31.0.0
2.31.0.02.31.0.0
2.31.0.02.31.0.0
2.31.0.02.32.0.0
2.31.0.02.32.0.0
2.31.0.02.32.0.0
2.31.0.02.32.0.0
2.31.0.02.32.0.0
2.31.0.02.32.0.0
2.31.0.02.32.0.0
2.31.1.02.32.0.0
2.31.1.02.32.0.0
2.31.1.02.32.0.0
2.31.1.02.32.0.0
2.31.1.02.32.0.0
2.32.0.02.33.0.0
2.32.0.02.33.0.0
2.32.0.02.33.0.0
2.32.0.02.33.0.0
2.32.0.02.33.0.0
2.33.1.02.33.1.0
2.33.1.02.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.

Need Help

5 Replies

  • saanah2019's avatar
    saanah2019
    Helper II
    6 years ago

    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. 

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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's avatar
      saanah2019
      Helper II
      6 years ago

      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's avatar
        saanah2019
        Helper II
        6 years ago

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

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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.