Forum Discussion
Creating End Date base on Next records Start Date - DAX
Anonymous ,
According to your description, my understanding is that you want to create a new column “End” from the next rows Start Date group by Case No.In this scenario, we can create an index column and then get the next row data based on the index. Please refer to the following steps:
1. Open Power Query Editor, then click the “Group By” button, then select “Group by” as Case No, Operation as All Rows. This step will group the data by Case No.
2. Then edit the following query as the below one, this step will create index for each group.
= Table.Group(#"Changed Type", {"Case No"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1), type table}})
3. After that, click on the button on the right of Count. Expand the table.
We will get a table like below, we can change the name of these columns.
4. Close & Apply these changes . Then change the data type of Index to Decimal Number, the data type of Start to Date/Time.
5. Then we can create a calculated column using the following query:
Old = IF(CALCULATE(MIN(Table1[Index])) = MAX(Table1[Index]),NOW(), CALCULATE(MIN(Table1[Start]),FILTER(Table1,Table1[Case No] = EARLIER(Table1[Case No]) && Table1[Index] = EARLIER(Table1[Index])+1)))
6. The result will like below:
Please refer to the attached pbix file.
Regards,
Is there any way to perform this operation completely in query editor? My table looks like this:
ID | Stage | Date
123 3 6/12/2018
123 4 4/9/2019
123 1 4/13/2019
I want to create an "end date" column that gives me stage 3 end date = 4/9/2019 & stage 1 end date = 6/12/2018.
I would like to do this entirey in query editor so I can run this code "{ Number.From([StartDate])..Number.From([EndDate]) }" to create the dates between.
If anyone has any insight it would be greatly appreciated.
Helper IAdam,
I wanted to do this too and looked at a way to do it purely through using M. If you look at the code below it takes parameters from the table when you're adding a new column.
EndDate = Table.AddColumn(#"Renamed columns", "End", each GetPreviousStartDateAsEnd([#"Employee ID"],[Start],[#"Final Day of Employment"]), type date),
The code below is commented and I hope it helps. This function uses another copy of your data (otherwise it creates a circular reference), although you only need the item you're getting dates for and the dates.
let
//## FUNCTION - Take Employee Id and Date of the current record and return the startdate of the next record.
//employeeId = Id of item I am interested in
//currentDate = To only return items after this date
//employmentEndDate = if they have left then use this to close the last period
GetPreviousStartAsEnd = (employeeId as text, currentDate as date, employmentEndDate) =>
let
//Get another copy of your source (slimmed down) to the columns you're interested in
Source = #"Salary Dates",
//filter the rows that you want to get the next start date for
#"Filtered rows" = Table.SelectRows(Source, each [Employee ID] = employeeId and [Start] > currentDate),
//Sort Column to ensure the items are in date order
Sorted = Table.Sort(#"Filtered rows", {{"Start", Order.Ascending}}),
//return the first one and if none remain then return the overall employeeEndDate (which will be null if they are still in-post).
#"Get Value" = List.First(Table.Column(Sorted,"Start"),employmentEndDate)
in
#"Get Value"
in
GetPreviousStartAsEnd