Previous row value but for a group of ID

[Prathameshsable]

Hi, 

I have the following data in the below table -

ID	Start	End
B2178	04-06-2023 00:09	04-06-2023 00:47
B2178	04-06-2023 00:09	04-06-2023 00:47
B2178	04-06-2023 00:47	04-06-2023 00:52
B2178	04-06-2023 00:55	04-06-2023 01:32
B2178	04-06-2023 00:55	04-06-2023 01:32
B7044	04-06-2023 01:32	04-06-2023 01:36
B7044	04-06-2023 01:32	04-06-2023 01:36
B7044	04-06-2023 01:36	04-06-2023 01:41
B7044	04-06-2023 01:36	04-06-2023 01:41
B1164	04-06-2023 02:19	04-06-2023 02:24
B1164	04-06-2023 02:19	04-06-2023 02:24
B1164	04-06-2023 02:24	04-06-2023 02:30
B1164	04-06-2023 02:24	04-06-2023 02:30
B1353	04-06-2023 03:13	04-06-2023 03:29
B1353	04-06-2023 03:13	04-06-2023 03:29
B2178	04-06-2023 03:38	04-06-2023 03:49
B2178	04-06-2023 03:38	04-06-2023 03:49
B2178	04-06-2023 03:49	04-06-2023 03:50

The ID colum has multiple start and end datetime, where-in basically the start datestime are in sequence. The ID column is nothing but a machine ID and the start and end are the runtimes of the machine, for every set of machine, I need to get the earliest start for every set of run in one column and the machine ID in another. Like for e.g. Machine ID B2178 has 2 set of runs on the same day.

The Output columns :

ID MAP which is nothing but for the B2178 and group of its run, it picks up the ID & concats with the date

ID runs 1 is picking up the earliest runtime for every ID and then filling it across for the where the ID has run for a specific period.

I can use a previous row methodology in PQ to check if previous row is blank but I want to find something dynamic in DAX itself which can give me a column to use, I want to further use these columns in creating Matrix visuals, hence the ask. 

I tried working out on Earlier function, but not working. So need help on solving this one. Its a pressing issue for me, so any help on this is appreciated.

Expected Output:

ID	Start	End	dd-mmm-yy	ID MAP	ID runs 1
B2178	04-06-2023 00:09	04-06-2023 00:47	04-Jun-23	B2178_04-Jun-23	B2178_04-Jun-23 00:09
B2178	04-06-2023 00:09	04-06-2023 00:47	04-Jun-23		B2178_04-Jun-23 00:09
B2178	04-06-2023 00:47	04-06-2023 00:52	04-Jun-23		B2178_04-Jun-23 00:09
B2178	04-06-2023 00:55	04-06-2023 01:32	04-Jun-23		B2178_04-Jun-23 00:09
B2178	04-06-2023 00:55	04-06-2023 01:32	04-Jun-23		B2178_04-Jun-23 00:09
B7044	04-06-2023 01:32	04-06-2023 01:36	04-Jun-23	B7044_04-Jun-23	B7044_04-Jun-23 01:32
B7044	04-06-2023 01:32	04-06-2023 01:36	04-Jun-23		B7044_04-Jun-23 01:32
B7044	04-06-2023 01:36	04-06-2023 01:41	04-Jun-23		B7044_04-Jun-23 01:32
B7044	04-06-2023 01:36	04-06-2023 01:41	04-Jun-23		B7044_04-Jun-23 01:32
B1164	04-06-2023 02:19	04-06-2023 02:24	04-Jun-23	B1164_04-Jun-23	B1164_04-Jun-23 02:19
B1164	04-06-2023 02:19	04-06-2023 02:24	04-Jun-23		B1164_04-Jun-23 02:19
B1164	04-06-2023 02:24	04-06-2023 02:30	04-Jun-23		B1164_04-Jun-23 02:19
B1164	04-06-2023 02:24	04-06-2023 02:30	04-Jun-23		B1164_04-Jun-23 02:19
B1353	04-06-2023 03:13	04-06-2023 03:29	04-Jun-23	B1353_04-Jun-23	B1353_04-Jun-23 03:13
B1353	04-06-2023 03:13	04-06-2023 03:29	04-Jun-23		B1353_04-Jun-23 03:13
B2178	04-06-2023 03:38	04-06-2023 03:49	04-Jun-23	B2178_04-Jun-23	B2178_04-Jun-23 03:38
B2178	04-06-2023 03:38	04-06-2023 03:49	04-Jun-23		B2178_04-Jun-23 03:38
B2178	04-06-2023 03:49	04-06-2023 03:50	04-Jun-23		B2178_04-Jun-23 03:38

[Prathameshsable · Accepted solution]

Hi @danextian - I tried this and it doesnt give me the expected results.

What I am trying to do here is if the current row of the ID matches the previous row if the ID then 'Blank' else contactenate ID & start date (format to dd-mmm-yy). Thats for the IDMap column.

The second column IDruns, just returns the earliest datetime of the IDMap column.

Please do not consider the end date in the calculation.

So basically in excel its a simple formula -

Considering column "E" as the ID & O as the start date - the below formulas are

IDMap =IF(E22=E21,"",E22&"_"&O22)

Output - B2178_04-Jun-23

And ID Runs1 = =IF(E2=E1,"",E2&"_"&O2&" "&TEXT(TIMEVALUE(TEXT(M2,"hh:mm")),"hh:mm"))
Output - B2178_04-Jun-23 00:09

Also, I have a issue in the above DAX wherein if I dynamically pick up a start and date, the above formulaes should take the take start datetime ascending as the initiation point for all its calculations? 

Any help here is thankful.

Regards,

Prathamesh

[danextian]

hI @Prathameshsable 

Try this calc column and work from here.

IDRuns2 =
VAR __DateTime = Table2[Start]
VAR __ID = Table2[ID]
VAR __PrevEnd =
    CALCULATE (
        MAX ( 'Table2'[End] ),
        FILTER (
            'Table2',
            'Table2'[End] <= EARLIER ( 'Table2'[Start] )
                && 'Table2'[ID] = EARLIER ( 'Table2'[ID] )
        )
    )
VAR __Table =
    FILTER (
        ADDCOLUMNS ( Table2, "@runs", IF ( __PrevEnd <> [Start], [Start] ) ),
        [ID] = __ID
    )
VAR __LastDateTime =
    MAXX ( FILTER ( __Table, [@runs] <= __Datetime ), [@runs] )
VAR __IDRuns =
    Table2[ID] & FORMAT ( __LastDateTime, "_dd-mmm-yy hh:mm" )
RETURN
    __IDRuns

Please note that your data shows 3sequences starting at 12:09, 12:55 (the end b efore this is 12:52) and 3:38 however your sample result shows just 2.

[Prathameshsable · Accepted solution]

Hi @danextian - I tried this and it doesnt give me the expected results.

What I am trying to do here is if the current row of the ID matches the previous row if the ID then 'Blank' else contactenate ID & start date (format to dd-mmm-yy). Thats for the IDMap column.

The second column IDruns, just returns the earliest datetime of the IDMap column.

Please do not consider the end date in the calculation.

So basically in excel its a simple formula -

Considering column "E" as the ID & O as the start date - the below formulas are

IDMap =IF(E22=E21,"",E22&"_"&O22)

Output - B2178_04-Jun-23

And ID Runs1 = =IF(E2=E1,"",E2&"_"&O2&" "&TEXT(TIMEVALUE(TEXT(M2,"hh:mm")),"hh:mm"))
Output - B2178_04-Jun-23 00:09

Also, I have a issue in the above DAX wherein if I dynamically pick up a start and date, the above formulaes should take the take start datetime ascending as the initiation point for all its calculations? 

Any help here is thankful.

Regards,

Prathamesh

[danextian · Accepted solution]

Hi @Prathameshsable ,

Going back to my initial response, is this supposed to be another sequence? Basing on the logic that its start is not the same as the end of the previous row, it should be. If not, then how do you identify which ones are not?

Also, I have a issue in the above DAX wherein if I dynamically pick up a start and date, the above formulaes should take the take start datetime ascending as the initiation point for all its calculations? If you're trying to pick up a date from a slicer, that will not affect your calculated columns. Calculated columns and tables update only when the underlying data has been refreshed or when they've been modified and not to a change in slicer selection.

[Prathameshsable]

This helps. Thanks for your help on this topic 🙂

[Prathameshsable]

Hi @danextian,

So let me explain the first part the ID is a machine ID which kind of is a machine which is common for multiple customers, so if you see 2 same datetime entries against the machine it, they are because there are multiple customers for which the machine was functioning. Hence 2 date times. However, the data coming from excel is manually sorted in ascending order and then based on the previous row of just the machine ID (without datetime) it just sees if the previous record contains the same machine ID (e.g. machine ID = 1 had 10 entries for 10 customers, it will still be flagged as the machine ID), and once the machine ID is changed from 1 to 2, the flag will showcase machine ID =2. And for tat entire set of 10 entries for machine Id 1, it will pick up the earliest datetime of the machine ID until it changed. It could be a thing that machine id 1 was used later in the day, so it will still be counted as a separate entry. So hope it clears the doubt.