Time Duration by Orden Status

[mrbajana]

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

 

[Anonymous · Accepted solution]

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!

[mrbajana]

Thanks a lot Kelly 

[parry2k]

@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.⚡