I have a table as follows:

Date / Name / OldValue / NewValue

I want a way to create a Start Date & End Date columns using any of the following : DAX , T-SQL or M Query.

Meaning; the StartDate is the [Date] and the End the date is the Date where the same person will change value.

Thanks in advance.

CodePudding user response：

Using Power Query M, you can follow these steps:

• In my demo data the columns came in sorted correctly, but you can use this code to ensure they are:

  #"Sort Table" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending}, {"Date", Order.Ascending}})

• Add an Index column > Add Column > Index Column > From 0 (It HAS to be from 0)

• Add Column > Custom Column > name End Date >

  if [Name] <> #"Added Index" [Name] {[Index] 1} then null else Date.AddDays(#"Added Index" [Date] {[Index] 1},-1)

• Right-click End Date > Replace Errors > Replace with null

• Adjust column order as needed

• Rename New Value column to "Value", rename Date column to "Start Date". Remove unneeded columns

Source and Output:

EDIT: I should add that this works okay on a small data set, but on a larger one you may run into performance issues. In that case you should duplicate the query, add an index starting at 0 for one query, and on the other query start an index at 1. Then you can merge the two together to extract the necessary dates and values.