Create start and end date columns based on milestone dates

[tonyclifton]

Hello community,

I have the following table:

| Project | Date       | Milestone Name | Milestone ID |
| ------- | ---------- | -------------- | ------------ |
| A       | 2019-02-08 | MS 1           | 1            |
| A       | 2019-02-27 | MS 2           | 2            |
| A       | 2019-04-08 | MS 3           | 3            |
| B       | 2018-01-15 | MS 2           | 2            |
| B       | 2018-02-01 | MS 4           | 4            |
| B       | 2018-03-20 | MS 6           | 6            |

I need to create two columns for Start and End Date based on the Date column, so that I can display them in a timeline chart.

Desired Output:

| Project | Date       | Milestone Name | Milestone ID | Start Date  | End Date   |
| ------- | ---------- | -------------- | ------------ | ----------- | ---------- |
| A       | 2019-02-08 | MS 1           | 1            | 2019-01-01  | 2019-02-08 |
| A       | 2019-02-27 | MS 2           | 2            | 2019-02-08  | 2019-02-27 |
| A       | 2019-04-08 | MS 3           | 3            | 2019-02-27  | 2019-04-08 |
| B       | 2018-01-15 | MS 2           | 2            | 2018-01-01  | 2018-01-15 |
| B       | 2018-02-01 | MS 4           | 4            | 2018-01-15  | 2018-02-01 |
| B       | 2018-03-20 | MS 6           | 6            | 2018-02-01  | 2018-03-20 |

For Milestone ID 1 (or in general the min Milestone ID grouped by Project) the start date should always be first day of the year based on the Date column of that Milestone and the Date column value of each Milestone should become the End Date.
For the other Milestones the Start Date is the previous Milestone Date.

I could not get near a solution so I am hoping for your ideas.

Thanks alot.

[Mariusz]

Hi @tonyclifton 

Please see M script below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcw9CoAwDAXgu2Qu5K/WOureybH0/tcwMdCiy4Pk473e4YQEVJEEhfiwo91syTBSoOwfFH9N9GZeqJb64uUbGxI71l/T0SRmA7NlniiEpAuLZYExHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, #"End Date" = _t, Milestone = _t, #"Milestone ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"End Date", type date}, {"Milestone", type text}, {"Milestone ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "previousDate", each let p = [Project], d = [End Date]
    in 
        List.Max(
            Table.SelectRows(
                #"Changed Type", 
                each [Project] = p and [End Date] < d
            )[End Date]
        ), 
        type date
    ),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Start Date", each if [previousDate] = null then Date.StartOfYear([End Date]) else [previousDate], type date),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Project", "Milestone", "Milestone ID", "Start Date"})
in
    #"Removed Other Columns"

Regards,
Mariusz

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

[tonyclifton]

@Mariusz  thanks alot this works for me. Any chance to see the same functionality in a DAX column/measure?

[Mariusz · Accepted solution]

Hi @tonyclifton 

You can do it as Column (Please see the below) or Measure, but its better to do it in Query Editor.

Start Date = 
VAR endDate = YourTable[End Date]
VAR startDate = CALCULATE(
    MAX( YourTable[End Date] ),
    ALLEXCEPT( YourTable, YourTable[Project] ),
    YourTable[End Date] < endDate
)
RETURN IF( ISBLANK( startDate ), DATE( YEAR( endDate ), 1, 1 ), startDate )

Many Thanks

Regards,
Mariusz

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

[tonyclifton]

Perfect. Thank you very much.