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 Help.

Marco

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • 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!

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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!
  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    mrbajana add following expression

     

    Time Change (min) = 
VAR __currentTime = Table[DateTime]
VAR __prevTime = CALCULATE ( MAX ( Table[DateTime] ), ALLEXCEPT ( Table, Table[Order_Id] ),
Table[DateTime] < __currentTime )
RETURN
DATEDIFF ( __currentTime, __prevTime, MINUTE )

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡