Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 No | Old Value | New Value | Start | End |
02386-X7F7S7 | (no value) | Submitted | 17/09/2018 9:16 | 18/09/2018 9:00 |
02386-X7F7S7 | Submitted | Automated Assessment | 18/09/2018 9:00 | 18/09/2018 9:02 |
02386-X7F7S7 | Automated Assessment | Collection | 18/09/2018 9:02 | 18/09/2018 9:02 |
02386-X7F7S7 | Collection | Collection | 18/09/2018 9:02 | 18/09/2018 9:06 |
02386-X7F7S7 | Collection | Planned for collection | 18/09/2018 9:06 | 21/09/2018 13:11 |
02386-X7F7S7 | Planned for collection | Sample received | 21/09/2018 13:11 | 21/09/2018 13:50 |
02386-X7F7S7 | Sample received | Sample received | 21/09/2018 13:50 | 21/09/2018 14:32 |
02386-X7F7S7 | Sample received | Negative confirmed | 21/09/2018 14:32 | 21/09/2018 14:32 |
02386-X7F7S7 | Negative confirmed | Negative | 21/09/2018 14:32 | Current 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...
Issue is when subsequent date is equal to the current Start.
Thanks
Solved! Go to Solution.
@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,
@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,
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
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)))
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |