-1

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.

adnane
  • 89
  • 9
  • 1
    As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Nov 02 '21 at 00:11
  • 2
    Does this answer your question? [Is there a way to access the "previous row" value in a SELECT statement?](https://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement) – Charlieface Nov 02 '21 at 00:22
  • I made the changes @DaleK – adnane Nov 02 '21 at 09:44
  • It didn't work for me @Charlieface – adnane Nov 02 '21 at 10:01

1 Answers1

0

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: enter image description here

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.

Jody Highroller
  • 999
  • 5
  • 12
  • I did run into performance issues. But how will duplicating the query solve it ? it'll just be doing the same thing twice ! – adnane Nov 02 '21 at 09:30