Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

help with rank and consecutive row time difference

I am having a tough time calculating my rank and time difference in seconds.

here is my data.

IDSTATUSSTEPSTEPTIME
1234changedwalkin3/29/2021 04:22:36 PM
1234changed signed consent3/29/2021 04:23:36 PM
1234changed pic taken3/29/2021 04:23:56 PM
1234changed blood withdran3/29/2021 04:25:36 PM
1236changed walkin3/29/2021 04:22:26 PM
1236changed signed consent3/29/2021 04:22:46 PM
1236changed pic taken3/29/2021 04:23:36 PM
1236changed blood withdran3/29/2021 04:27:36 PM

 

 

Now I am trying to calculate rank and time difference in seconds

 

IDSTATUSSTEPSTEPTIMEranktime in sec
1234changedwalkin3/29/2021 04:22:36 PM10
1234changed signed consent3/29/2021 04:23:36 PM260
1234changed pic taken3/29/2021 04:23:56 PM320
1234changed blood withdran3/29/2021 04:25:56 PM4120
1236changed walkin3/29/2021 04:22:26 PM10
1236changed signed consent3/29/2021 04:22:46 PM220
1236changed pic taken3/29/2021 04:23:36 PM350
1236changed blood withdran3/29/2021 04:27:36 PM4240

 

Can anyone help us with the rank , I am using rankx and dense combined but unable to get it work individually for the step and datediff also is not working when using earlier step field. Pleas help.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    I think you can create a column to meet your needs.

     

    time between step =

    var _diff=DATEDIFF(

                        CALCULATE(MAX('Table'[STEPTIME]),FILTER('Table','Table'[STEPTIME]<EARLIER('Table'[STEPTIME]) && 'Table'[ID]=EARLIER('Table'[ID]))),

                           'Table'[STEPTIME],

                              SECOND)

    return IF(_diff=BLANK(),0,_diff)

     

    The effect is as shown:

    You saied that some milli second ones it is not calculating correctly. Because the min interval of DATEDIFF DAX is second. So there will be a little error.

      

    Best Regards

    Community Support Team _ Ailsa Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Can anyone help please really cant figure this out.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    time between step =  -1* (datediff('data'[steptime]),

    calculate(max('data'[steptime]),

    filter('data',data[id]=earlier('data'[id]) && 'data'[steptime]<'data'[steptime])

    )) SECOND)

     

    This is working but for some milli second ones it is not calculating correctly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    I think you can create a column to meet your needs.

     

    time between step =

    var _diff=DATEDIFF(

                        CALCULATE(MAX('Table'[STEPTIME]),FILTER('Table','Table'[STEPTIME]<EARLIER('Table'[STEPTIME]) && 'Table'[ID]=EARLIER('Table'[ID]))),

                           'Table'[STEPTIME],

                              SECOND)

    return IF(_diff=BLANK(),0,_diff)

     

    The effect is as shown:

    You saied that some milli second ones it is not calculating correctly. Because the min interval of DATEDIFF DAX is second. So there will be a little error.

      

    Best Regards

    Community Support Team _ Ailsa Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.