Forum Discussion
Create start and end date columns based on milestone dates
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.
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.
Mariusz thanks alot this works for me. Any chance to see the same functionality in a DAX column/measure?
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.Perfect. Thank you very much.