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 

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

tameemyousaf's avatar
tameemyousaf
Helper I
5 years ago

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

 

 

  • 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