New column with value of next row grouped/filtered by multiple columns

[Opilio2]

Hi everyone, 

since I'm no DAX pro, I urgently need your help with the following issue. 

Background: I'm analyzing the use of electronic devices. To be specific, I want to know how often users start different sequential programs in the daily use. That is, if a certain program on device A on day x is started, what is the next program?

Data basically looks like this: 

Device ID	Timestamp	Program ID
A	2022-01-01 14:38	ID1
B	2022-01-01 09:12	ID 2
A	2022-01-03 19:45	ID 3
C	2022-01-04 19:45	ID 2
B	2022-01-01 11:03	ID 2
B	2022-01-01 22:54	ID 1
A	2022-01-03 23:09	ID 1

I need a new column "NextProgramSameDay"  which, for every distinct device and every single day, contains the program ID of the directly following program on the same day:

Device ID	Timestamp	Program	NextProgramSameDay	Comment (for clarification, not to be included in the dataframe)
A	2022-01-01 14:38	ID 1		no next program because no additional program was started on the same day in device A
B	2022-01-01 09:12	ID 2	ID 2	Next program on device B on same day is program 2 at 11:03
A	2022-01-03 19:45	ID 3	ID 1	Next program on device A on  same day is program 1 at 23:09
C	2022-01-04 19:45	ID 2		no next program because no additional program was started on the same day in device C
B	2022-01-01 11:03	ID 2	ID 1	Next program on same day in device B is program 1 at 22:54
B	2022-01-01 22:54	ID 1		no next program because no additional program was started on the same day in device B
A	2022-01-03 23:09	ID 1		no next program because no additional program was started on the same day in device A

Can anyone tell me how to accomplish this? 

Thanks a lot in advance!!!

[tamerj1 · Accepted solution]

Hi @Opilio2 
Yes you are right. I missed that we are dealing with time rather than date. Please use

NextProgramSameDay column = 
VAR CurrentTimeStamp = 'Table'[Timestamp]
VAR CurrentDate = DATE ( YEAR ( CurrentTimeStamp ), MONTH ( CurrentTimeStamp ), DAY ( CurrentTimeStamp ) )
VAR FilteredDeviceTable =
    FILTER (
        CALCULATETABLE (
            'Table',
            ALLEXCEPT ( 'Table', 'Table'[Device ID] )
        ),
        DATE ( YEAR ( 'Table'[Timestamp] ), MONTH ( 'Table'[Timestamp] ), DAY ( 'Table'[Timestamp] ) ) = CurrentDate
            && 'Table'[Timestamp] > CurrentTimeStamp
    )
VAR NextTimeStamp =
    MINX (
        FilteredDeviceTable,
        'Table'[Timestamp]
    )
VAR NextProgram =
    MINX ( FILTER ( FilteredDeviceTable, 'Table'[Timestamp] = NextTimeStamp ), 'Table'[Program ID] )
RETURN
    NextProgram

[tamerj1]

@Opilio2 

Try

NextProgramSameDay column =
VAR CurrentDate = Table[Timestamp]
RETURN
    COUNTROWS (
        FILTER (
            CALCULATETABLE (
                Table,
                ALLEXCEPT ( Table, Table[Device ID], Table[Timestamp] )
            ),
            Table[Timestamp] > CurrentDate
        )
    )

[Opilio2]

Unfortunately, this does not work but returns an empty column. 

[tamerj1]

Hi @Opilio2 

if you are looking for a column you can use

NextProgramSameDay column =
COUNTROWS (
    FILTER (
        CALCULATETABLE (
            Table,
            ALLEXCEPT ( Table, Table[Device ID], Table[Timestamp] )
        ),
        Table[Timestamp] > EARLIER ( Table[Timestamp] )
    )
)

[Opilio2]

Hey @tamerj1 ,
many thanks for your fast reply - this doesn't seem to be the solution, though; your suggestion only delivers blanks.
Additionally, I might have been mistakable, though- I'm looking for the following program ID/ name, given a certain deive ID and the date.

[tamerj1]

Are you creating a calculated column or w measure?