Forum Discussion

Venkateswara_ra's avatar
Venkateswara_ra
Helper I
6 years ago

Need DAX to calculate time difference between rows in same column

I have employee login date and time from the login date and time i need calculate log out date and time.

In a day employee login first time as some activity and next time he's login in different activity so that i need to calculate between time so that will be logout time for the previous activity

data looks like

TimeempIdactivity OutPut column I need as 
6/16/2020  14:09:17 PM245APS995 seconds
6/16/2020  14:25:17 PM245KS2640 seconds
6/16/2020  15:09:17 PM245APS7200 seconds
6/16/2020  17:09:17 PM245TG0 seconds ( employee last login will consider as 0 seconds 
6/14/2020  14:11:17 PM999APS1380 sec
6/14/2020  14:35:17 PM999KS2340 sec
6/14/2020  15:09:17 PM999KT0 seconds ( employee last login will consider as 0 seconds 
6/12/2020  14:09:17 PM845APS3600 seconds
6/12/2020  15:09:17 PM845TG0 seconds ( employee last login will consider as 0 seconds 
6/11/2020  17:09:17 PM777KS3600 seconds
6/11/2020  18:09:17 PM777KT0 seconds ( employee last login will consider as 0 seconds
6/10/2020  14:09:17 PM245APS995 seconds
6/10/2020  14:25:17 PM245KS2640 seconds
6/10/2020  15:09:17 PM245APS7200 seconds
6/07/2020  14:11:17 PM999APS1380 sec
6/07/2020  14:35:17 PM999KS2340 sec
6/07/2020  15:09:17 PM999KT0 seconds ( employee last login will consider as 0 seconds 
6/02/2020  14:09:17 PM845APS3600 seconds
6/02/2020  15:09:17 PM845TG0 seconds ( employee last login will consider as 0 seconds 
6/03/2020  17:09:17 PM777KS3600 seconds
6/03/2020  18:09:17 PM777KT0 seconds ( employee last login will consider as 0 seconds
Need Help
Show and Tell

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    6 years ago

    Venkateswara_ra 

     

    Please see the measures below

    Column = 
VAR newtime=CALCULATE(MIN(Sheet13[Time]),FILTER(Sheet13,DATEVALUE(Sheet13[Time])=DATEVALUE(EARLIER('Sheet13'[Time]))&&Sheet13[empId]=EARLIER(Sheet13[empId])&&Sheet13[Time]>EARLIER(Sheet13[Time])))

return if(ISBLANK(newtime),0,newtime-Sheet13[Time])*24*60*60

    However the output is slight different from yours.

     

    14:11-14:35, 24 mins, 1440 seconds, why your output is 1380?

    14:35-15:09 34 mins, 2440 seconds, why your output is 2340?

     

    Please let me know if I misunderstand your request.

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Venkateswara_ra , Try a new column like

    if( [activity] ="TG",0 , datediff([Time],minx(filter(Table,[empId]=earlier(empId) && [Date] = earlier([Date])),[Time]),second))

     

     [Date] = earlier([Date]) is option for that create a date

    date =[time].date