DAX: Extract Start Date from one column and End date from another column if flag is consecutively 1

[Anonymous]

Hi,

I have a table as follows:

Status	Timestamp	Job Name	Failure Type
Internal Error	06-12-2019 07:53	E	Continuous
Internal Error	06-12-2019 07:48	E	Continuous
Internal Error	06-12-2019 07:43	E	Continuous
Internal Error	06-12-2019 07:38	E	Continuous
Internal Error	06-12-2019 07:33	E	Continuous
Internal Error	06-12-2019 07:55	D	Continuous
Internal Error	06-12-2019 07:50	D	Continuous
Internal Error	06-12-2019 07:45	D	Continuous
Internal Error	06-12-2019 07:40	D	Continuous
Internal Error	06-12-2019 07:35	D	Continuous
Internal Error	06-12-2019 07:30	D	Continuous
Internal Error	06-12-2019 04:40	E	Discrete
Internal Error	06-12-2019 07:55	C	Continuous
Internal Error	06-12-2019 07:50	C	Continuous
Internal Error	06-12-2019 07:45	C	Continuous
Internal Error	06-12-2019 07:40	C	Continuous
Internal Error	06-12-2019 12:40	E	Discrete
Internal Error	06-12-2019 07:35	C	Continuous
Internal Error	06-12-2019 07:30	C	Continuous
Internal Error	06-12-2019 07:53	F	Continuous
Internal Error	06-12-2019 07:48	F	Continuous
Internal Error	06-12-2019 07:43	F	Continuous
Internal Error	06-12-2019 07:38	F	Continuous
Internal Error	06-12-2019 07:33	F	Continuous
Internal Error	06-12-2019 07:52	I	Continuous
Internal Error	06-12-2019 07:47	I	Continuous
Internal Error	06-12-2019 07:42	I	Continuous
Internal Error	06-12-2019 07:37	I	Continuous
Internal Error	06-12-2019 07:32	I	Continuous
Internal Error	06-12-2019 07:55	L	Continuous
Internal Error	06-12-2019 07:50	L	Continuous
Internal Error	06-12-2019 07:45	L	Continuous
Internal Error	06-12-2019 07:40	L	Continuous
Internal Error	06-12-2019 07:35	L	Continuous
Internal Error	06-12-2019 07:30	L	Continuous
Internal Error	06-12-2019 23:53	K	Continuous
Internal Error	06-12-2019 23:43	K	Continuous
Internal Error	06-12-2019 23:33	K	Continuous
Internal Error	06-12-2019 23:23	K	Continuous
Internal Error	06-12-2019 23:13	K	Continuous
Internal Error	06-12-2019 23:03	K	Continuous
Internal Error	06-12-2019 22:53	K	Continuous
Internal Error	06-12-2019 22:43	K	Continuous
Internal Error	06-12-2019 22:33	K	Continuous
Internal Error	06-12-2019 22:23	K	Continuous
Internal Error	06-12-2019 22:13	K	Continuous
Internal Error	06-12-2019 22:03	K	Continuous
Internal Error	06-12-2019 21:53	K	Continuous
Internal Error	06-12-2019 21:38	K	Continuous
Internal Error	06-12-2019 21:28	K	Continuous
Internal Error	06-12-2019 21:18	K	Continuous
Internal Error	06-12-2019 21:08	K	Continuous
Internal Error	06-12-2019 20:58	K	Continuous
Internal Error	06-12-2019 20:48	K	Continuous
Internal Error	06-12-2019 20:38	K	Continuous
Internal Error	06-12-2019 20:28	K	Continuous
Internal Error	06-12-2019 20:18	K	Continuous
Internal Error	06-12-2019 20:08	K	Continuous
Internal Error	06-12-2019 19:58	K	Continuous
Internal Error	06-12-2019 19:48	K	Continuous
Internal Error	06-12-2019 19:18	K	Continuous
Internal Error	06-12-2019 19:08	K	Continuous
Internal Error	06-12-2019 18:58	K	Continuous
Internal Error	06-12-2019 18:48	K	Continuous
Internal Error	06-12-2019 18:38	K	Continuous
Internal Error	06-12-2019 18:28	K	Continuous
Internal Error	06-12-2019 18:18	K	Continuous
Internal Error	06-12-2019 18:08	K	Continuous
Internal Error	08-12-2019 10:33	K	Continuous
Internal Error	08-12-2019 10:23	K	Continuous
Internal Error	08-12-2019 10:13	K	Continuous
Internal Error	08-12-2019 10:03	K	Continuous
Internal Error	08-12-2019 09:53	K	Continuous
Internal Error	08-12-2019 09:43	K	Continuous

I'd like to create a seperate table from this where for every 'Job Name' I have different periods defined with 'Start Date' and 'End Date' extracted from the 'Timestamp' field.
Start Date is the first timestamp for that job when the Failue Type is Continuous, 
End Date is the last timestamp for that job when the Failure Type is Continuous.

This defines a period. And there can be many periods for that job. The table obviously has to be sorted before finding these 'continuous' periods broken by in between 'Discrete' Failure Type.

So I need the following table calculated in the end:

Job Name	Start Date	End Date
E	06-12-2019 07:33	06-12-2019 07:53
D	06-12-2019 07:30	06-12-2019 07:55
C	06-12-2019 07:30	06-12-2019 07:55
F	06-12-2019 07:33	06-12-2019 07:53
I	06-12-2019 07:32	06-12-2019 07:52
L	06-12-2019 07:30	06-12-2019 07:55
K	06-12-2019 18:08	06-12-2019 23:53
K	08-12-2019 09:43	08-12-2019 10:33

However I have achieved this in python, I can't seem to be able to write DAX for the same! 

[Jotad710]

https://www.myonlinetraininghub.com/extract-start-and-end-dates-with-power-query

[v-eachen-msft]

Hi @Anonymous ,

You could create a rank column to set groups and get the value.

Then use MIN() and MAX() functions to get start date nad end date.

Here is the result visual, I used a column "BasedDate" to group up the table.

Here is my test file for your reference.

[Anonymous]

Hi @v-eachen-msft 

It works for now, but I guess it won't work if I have multiple continuous periods for the same job in same day! (So what if both periods were on 6th Dec for job K?)
In the sample data I didn't inlcude that case, but it can happen in the future. In that case grouping records on day and taking the minimum of timestamp in that day also won't work, right?

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

I edit the file again, add an index and then create a new column "rank4" to get date.

You could refer to my test file.

[az38]

Hi @Anonymous 

try new table

NewTable = summarize('Table1';Table1[Job Name];"StartDate";min(Table1[Timestamp]);"EndDate";max(Table1[Timestamp]))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Hi @az38 ,

If only it were that simple!

As I said, between the minTime and maxTime for every job, I have multiple continuous periods defined by the timestamp and Failure Type.

Here is a part of table and comments for more clarity describing what I'm saying.

Source Table:

Status	Timestamp	Job Name	Failure Type	Comments
Internal Error	06-12-2019 23:53	K	Continuous	Period 2 Ends
Internal Error	06-12-2019 23:43	K	Continuous	|
Internal Error	06-12-2019 23:33	K	Continuous	|
Internal Error	06-12-2019 23:23	K	Continuous	|
Internal Error	06-12-2019 23:13	K	Continuous	|
Internal Error	06-12-2019 23:03	K	Continuous	|
Internal Error	06-12-2019 22:53	K	Continuous	|
Internal Error	06-12-2019 22:43	K	Continuous	|
Internal Error	06-12-2019 22:33	K	Continuous	|
Internal Error	06-12-2019 22:23	K	Continuous	|
Internal Error	06-12-2019 22:13	K	Continuous	|
Internal Error	06-12-2019 22:03	K	Continuous	|
Internal Error	06-12-2019 21:53	K	Continuous	|
Internal Error	06-12-2019 21:38	K	Continuous	|
Internal Error	06-12-2019 21:28	K	Continuous	|
Internal Error	06-12-2019 21:18	K	Continuous	|
Internal Error	06-12-2019 21:08	K	Continuous	|
Internal Error	06-12-2019 20:58	K	Continuous	|
Internal Error	06-12-2019 20:48	K	Continuous	|
Internal Error	06-12-2019 20:38	K	Continuous	|
Internal Error	06-12-2019 20:28	K	Continuous	|
Internal Error	06-12-2019 20:18	K	Continuous	|
Internal Error	06-12-2019 20:08	K	Continuous	|
Internal Error	06-12-2019 19:58	K	Continuous	|
Internal Error	06-12-2019 19:48	K	Continuous	|
Internal Error	06-12-2019 19:18	K	Continuous	|
Internal Error	06-12-2019 19:08	K	Continuous	|
Internal Error	06-12-2019 18:58	K	Continuous	|
Internal Error	06-12-2019 18:48	K	Continuous	|
Internal Error	06-12-2019 18:38	K	Continuous	|
Internal Error	06-12-2019 18:28	K	Continuous	|
Internal Error	06-12-2019 18:18	K	Continuous	|
Internal Error	06-12-2019 18:08	K	Continuous	Period 2 Starts
Internal Error	06-12-2019 11:18	K	Discrete	|
Internal Error	06-12-2019 11:08	K	Discrete	|
Internal Error	08-12-2019 10:33	K	Continuous	Period 1 Ends
Internal Error	08-12-2019 10:23	K	Continuous	|
Internal Error	08-12-2019 10:13	K	Continuous	|
Internal Error	08-12-2019 10:03	K	Continuous	|
Internal Error	08-12-2019 09:53	K	Continuous	|
Internal Error	08-12-2019 09:43	K	Continuous	Period 1 Starts

Output Table expected:

Job Name	Start Date	End Date
K	06-12-2019 18:08	06-12-2019 23:53
K	08-12-2019 09:43	08-12-2019 10:33

I hope it's clearer.