Forum Discussion

GKJARC's avatar
GKJARC
Resolver I
2 years ago
Solved

Get previous row's value

Hi all,

 

I'm looking for a way get the timestamp of the previous row onto the current row. So that when they're on the same row, I can calculate the time difference for each user.

Example:

UserIDTimestampPrevious row's timestampCalculated time difference (mm:ss)
00114:50:00  
00114:52:3014:50:0002:30
00114:55:3014:52:3003:00
00114:58:1014:55:3002:40
00208:58:10  
00209:00:0008:58:1001:50
00209:01:0009:00:0001:00
00209:01:2009:01:0000:20

 

So the first and second column is an existing column in the report.

The third column is what I need.

The fourth column I can manage once I have the third column in place.

How to create the second column using DAX, without using the Power Query Editor?


Any help would be appreciated, thanks!

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    OFFSET function (DAX) - DAX | Microsoft Learn

     

    Previous CC = 
SELECTCOLUMNS (
    OFFSET (
        -1,
        Data,
        ORDERBY ( Data[Timestamp], ASC ),
        ,
        PARTITIONBY ( Data[UserID] ),
        MATCHBY ( Data[UserID], Data[Timestamp] )
    ),
    Data[Timestamp]
)

     

    Diff CC = 
IF ( NOT ISBLANK ( Data[Previous CC] ), Data[Timestamp] - Data[Previous CC] )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    OFFSET function (DAX) - DAX | Microsoft Learn

     

    Previous CC = 
SELECTCOLUMNS (
    OFFSET (
        -1,
        Data,
        ORDERBY ( Data[Timestamp], ASC ),
        ,
        PARTITIONBY ( Data[UserID] ),
        MATCHBY ( Data[UserID], Data[Timestamp] )
    ),
    Data[Timestamp]
)

     

    Diff CC = 
IF ( NOT ISBLANK ( Data[Previous CC] ), Data[Timestamp] - Data[Previous CC] )