Forum Discussion
mrbajana
Helper III
6 years agoTime 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...
- Anonymous6 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,
KellyDid I answer your question? Mark my post as a solution!
Anonymous
6 years agoNot applicable
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
Helper III
6 years agoThanks a lot Kelly