Difference Between Two Dates in Single Column

[tameemyousaf]

Hi,

I have data that has timestamp in sigle row. I want to calculate the difference (in seconds or minuites) between two timestamps and need to show in another column or measure. Can anyone please help. Below is the screenshot of data.

[Anonymous · Accepted solution]

Hi @tameemyousaf ,

Check the formula.

Column = 
var lasttime = CALCULATE(MAX('Table'[timestamp]),FILTER('Table','Table'[timestamp]<EARLIER('Table'[timestamp])))
return
IF(ISBLANK(lasttime),BLANK(),DATEDIFF(lasttime,'Table'[timestamp],MINUTE))

Best Regards,

Jay

[Fowmy]

@tameemyousaf 

Can you explain with an example based on your sample data?

[tameemyousaf]

@Fowmy I need such type of results. The difference in seconds.

[Fowmy]

@tameemyousaf 

You can add the following New Column to your table to get the time difference in seconds.

Difference = 
var __datetime = Table20[Date] + Table20[Time]
var __predatetime = 
    MAXX(
        FILTER(
            ADDCOLUMNS(
                Table20,
                "@DateTime",
                    Table20[Date] + Table20[Time]
            ),
            [@DateTime] < __datetime
        ),
        [@DateTime]
    )
return
DATEDIFF( __predatetime,__datetime,SECOND )

[tameemyousaf]

@Fowmy Thanks for your reply. I have only one timestamp column. In this case what will be the value of _dateTime variable.

[Fowmy]

@tameemyousaf 

This should work:

Difference = 
var __datetime = Table20[DateTime]
var __predatetime = 
    MAXX(
        FILTER(
            ALL(Table20[DateTime]),
            Table20[DateTime] < __datetime
        ),
        Table20[DateTime]
    )
return
    DATEDIFF( __predatetime,__datetime,SECOND )

[ryan_mayu]

@tameemyousaf 

you can try this

Column = 
VAR last=MAXX(FILTER('Table','Table'[Timestamp]<EARLIER('Table'[Timestamp])),'Table'[Timestamp])
return if(ISBLANK(last),0,round(('Table'[Timestamp]-last)*3600*24,0))

pls see the attachment below