Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Creating End Date base on Next records Start Date - DAX

Hi 

 

I want to create as Column for each row as End Date from the next rows Start Date grouped by Case No.  

 

Bit new to DAX, how can this be achievied?

 

"End" is the column i require.

 

Sample Date below:

 

Case NoOld ValueNew ValueStart End
02386-X7F7S7(no value)Submitted17/09/2018 9:1618/09/2018 9:00
02386-X7F7S7SubmittedAutomated Assessment18/09/2018 9:0018/09/2018 9:02
02386-X7F7S7Automated AssessmentCollection18/09/2018 9:0218/09/2018 9:02
02386-X7F7S7CollectionCollection18/09/2018 9:0218/09/2018 9:06
02386-X7F7S7CollectionPlanned for collection18/09/2018 9:0621/09/2018 13:11
02386-X7F7S7Planned for collectionSample received21/09/2018 13:1121/09/2018 13:50
02386-X7F7S7Sample receivedSample received21/09/2018 13:5021/09/2018 14:32
02386-X7F7S7Sample receivedNegative confirmed21/09/2018 14:3221/09/2018 14:32
02386-X7F7S7Negative confirmedNegative21/09/2018 14:32Current DateTime

 

so far upto

 

EndDate =
VAR curr = Table1[Start ]
VAR currCase = Table1[Case No]
RETURN
    MINX (
        FILTER ( Table1, Table1[Case No] = currCase && Table1[Start ] > curr ),
        Table1[Start ]
    )

But the Output does looks what I need... 

 

 

output.JPG

 

Issue is when subsequent date is equal to the current Start.

 

 

Thanks

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee


@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.
1.png

 

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}})
2.png

3. After that, click on the button on the right of Count. Expand the table.
3.png

 

We will get a table like below, we can change the name of these columns.
4.png

 

 

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:
6.png

 

Please refer to the attached pbix file.

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yuezhe-msft
Employee
Employee


@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.
1.png

 

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}})
2.png

3. After that, click on the button on the right of Count. Expand the table.
3.png

 

We will get a table like below, we can change the name of these columns.
4.png

 

 

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:
6.png

 

Please refer to the attached pbix file.

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I am currently trying this and getting the following - I have downloaded your sample file, matched formats etc - currently every column is text format coming out of powerquery 

apuype_0-1669397182313.png

apuype_1-1669397234427.png

Any guidance would be appreciated! 

 

Thank you 

 

Thank you for this method - I would like to create the calculated column below in the M language in Power Query Editor? How to do this? Thanks

 

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)))  

 

Anonymous
Not applicable

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. 

 

Adam,

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.