Forum Discussion

tonyclifton's avatar
tonyclifton
Icon for Helper III rankHelper III
7 years ago
Solved

Create start and end date columns based on milestone dates

Hello community,   I have the following table: | Project | Date | Milestone Name | Milestone ID | | ------- | ---------- | -------------- | ------------ | | A | 2019-02-08 | MS 1 ...
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    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.

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
7 years ago

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's avatar
    tonyclifton
    Icon for Helper III rankHelper III
    7 years ago

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

    • Mariusz's avatar
      Mariusz
      Icon for Community Champion rankCommunity Champion
      7 years ago

      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's avatar
        tonyclifton
        Icon for Helper III rankHelper III
        7 years ago

        Perfect. Thank you very much.