Forum Discussion

tameemyousaf's avatar
tameemyousaf
Helper I
5 years ago
Solved

Difference Between Two Dates in Single Column

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 h...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
Fowmy
Super User
5 years ago

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's avatar
tameemyousaf
Helper I
5 years ago

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

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    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

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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 )