Forum Discussion

mrbajana's avatar
mrbajana
Icon for Helper III rankHelper III
6 years ago
Solved

Time Duration by Orden Status

I need a calculated column that gives me the duration in minutes from one state to another state for each order as the column that I have in green in the attached image.   Thanks a lot for your Hel...
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi mrbajana ,

     

    If the datetime is ordering ascending by ID,you can create a calculated column as below;

     

    Timelastchange(Min)1 = 
    var _previous=CALCULATE(MAX('Table'[DateTime]),FILTER('Table','Table'[Order_ID]=EARLIER('Table'[Order_ID])&&'Table'[DateTime]<EARLIER('Table'[DateTime])))
    Return
    IF(_previous=BLANK(),0,DATEDIFF(_previous,'Table'[DateTime],MINUTE))

     

    Otherwise first create an index column in query editor;

    Then create a calculated column as below:

     

    Timelastchange(Min)2 = 
    var _last=CALCULATE(MAX('Table'[DateTime]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1&&'Table'[Order_ID]=EARLIER('Table'[Order_ID])))
    Return
    IF(_last=BLANK(),0,DATEDIFF(_last,'Table'[DateTime],MINUTE))

     

    And you will see:

    Or you can create 2 measures as below:

     

    _Timelastchange(Min)1 = 
    var _last=MAXX(FILTER(ALL('Table'),'Table'[Order_ID]=MAX('Table'[Order_ID])&&'Table'[DateTime]<MAX('Table'[DateTime])),'Table'[DateTime])
    Return
    IF(_last=BLANK(),0,DATEDIFF(_last,MAX('Table'[DateTime]),MINUTE))
    _Timelastchange(Min)2 = 
    var _last=CALCULATE(MAX('Table'[DateTime]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])-1&&'Table'[Order_ID]=MAX('Table'[Order_ID])))
    Return
    IF(_last=BLANK(),0,DATEDIFF(_last,MAX('Table'[DateTime]),MINUTE))

     

    For the related .pbix file,pls click here.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!