Forum Discussion

Kumshan450's avatar
Kumshan450
Helper III
8 years ago

Calculate difference between two date/time values using current row minus previous row

how to calculate the time difference between two consecutive rows (current row - previous row) in a Column data

 

COLLECTION DTM In sample data. 

 

Thereafter i want to check whether the difference in timing less than one minute. IF its less than one minute then 1 otherwise 0

 

Note: I have 20 lakh rows of data. I want a solution which will fit the data size.

Need Help

4 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    HI Kumshan450

     

    If you add the following calculated column to your table, it will show the number of seconds between the current and previous row, which you can then use in an IF statement to 

     

    Seconds Diff = 
VAR StateName = '2018'[State Name]
VAR BranchName = '2018'[Branch Name] 
VAR CurrentCollectionDTM = '2018'[Collection DTM]
VAR PreviousCollecitonDTM = 
            MAXX(
                    FILTER(
                        '2018',
                        '2018'[State Name] = StateName &&
                        '2018'[Branch Name] = BranchName && 
                        '2018'[Collection DTM] < CurrentCollectionDTM
                    ),
                    '2018'[Collection DTM]
                    )
RETURN IF( DATEDIFF(PreviousCollecitonDTM,CurrentCollectionDTM,SECOND)<60,1,0)