Create new table from existing table

[clteh]

hi, 

i would like to create a new logon/logoff table from existing table. 

new table will find the matching logout_dt for a particular user.

If there is no logout prior to the next logon, logout_dt = next logon_dt. 

existing table:

user	logon_dt	logout_dt
userA	2022-11-22 08:10:00
userB	2022-11-22 08:20:00
userC	2022-11-22 08:30:00
userA		2022-11-22 12:10:00
userA	2022-11-22 13:00:00
userB	2022-11-22 13:20:00

New table

user	logon_dt	logout_dt	duration [hh:mm]
userA	2022-11-22 08:10:00	2022-11-22 12:10:00	04:00
userB	2022-11-22 08:20:00	2022-11-22 13:20:00	05:00
userC	2022-11-22 08:30:00
userA	2022-11-22 13:00:00
userB	2022-11-22 13:20:00

thanks 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

New table = 
VAR _logoutdatecolumn =
    ADDCOLUMNS (
        Data,
        "@logout_dt",
            VAR _nextlogondt =
                MINX (
                    FILTER (
                        Data,
                        Data[user] = EARLIER ( Data[user] )
                            && Data[logon_dt] > EARLIER ( Data[logon_dt] )
                    ),
                    Data[logon_dt]
                )
            VAR _nextlogoutdt =
                MINX (
                    FILTER (
                        Data,
                        Data[user] = EARLIER ( Data[user] )
                            && OR ( Data[logon_dt] > EARLIER ( Data[logon_dt] ), ISBLANK ( Data[logon_dt] ) )
                            && Data[logout_dt] < _nextlogondt
                    ),
                    Data[logout_dt]
                )
            RETURN
                IF ( _nextlogoutdt <> BLANK (), _nextlogoutdt, _nextlogondt )
    )
VAR _durationhrcolumn =
    ADDCOLUMNS (
        _logoutdatecolumn,
        "@durationmin", DATEDIFF ( Data[logon_dt], [@logout_dt], MINUTE )
    )
RETURN
    FILTER ( _durationhrcolumn, Data[logon_dt] <> BLANK () )

[clteh]

thanks. it is working.