Calculate the duration between 2 records based on different steps

[ichang2021]

Hi,

I wonder if this is possible.  I'd like to calculate the duration between 2 records based on different steps such as New Step and Old Step. Below are two examples with a screenshot to explain what I want to achieve. Thank you!

Example 1

The duration between 1.1 New Step is NEW and 1.2 Old Step is NEW

In other words, when the New Step is changed to Old Step, it means the work is moved to next step. The expected results is the difference between Work Date + Work Time for 1.2 Old Step is NEW and Work Date Work Time for 1.1 Old Step is NEW

Custom_Duration = Thursday, December 20, 2018, 1417 – Wednesday, December 19, 2018 1037

Example 2

The duration between 2.1 New Step is ASB and 2.2 Old Step is ASB

In other words, when the New Step is changed to Old Step, it means the work is moved to next step. The expected results is the difference between Work Date + Work Time for 2.2 Old Step is ASB and Work Date Work Time for 2.1 Old Step is ASB

Custom_Duration = Friday, January 11, 2019, 1550 – Friday, January 11, 2019, 0907  

[sevenhills · Accepted solution]

It looks like all you need is difference between two record of date entries, for a given work order

1. Create a calculated column as Date + Time and use it

WorkDateTime = 'Table'[WorkDate] + 'Table'[WorkTime]

2. Use Dax measure for diff calc ~ Elapsed Time. Refine to your needs, as I did as text

Elapsed Time - Diff in curr and prev = 

var _CurValWorkOrder = SELECTEDVALUE('Table'[WorkOrder])
var _CurValWorkDateTime = SELECTEDVALUE('Table'[WorkDateTime])
var _PrevValWorkDateTime = Calculate ( Max( 'Table'[WorkDateTime]), filter ( ALLSELECTED('Table'), 'Table'[WorkOrder] = _CurValWorkOrder && 'Table'[WorkDateTime] < _CurValWorkDateTime))
var _Diff = _CurValWorkDateTime - _PrevValWorkDateTime

var _days = if (_Diff >= 1, int(_Diff), blank())
var _h = int( (_Diff - _days) * 24) 
var _mm = (_diff * 24 * 60) - (_days * 24 * 60) - (_h * 60)

var _Displaytext = if (_days > 1, Format(_days, "00") & " days ") & Format(_h, "00") & " hours " & Format(_mm, "00") & " minutes"

Return If (not ISBLANK(_PrevValWorkDateTime), _Displaytext)

3. Optional: I added two extra measure in case for debugging

Prev Step DAX check = 

var _CurValWorkOrder = SELECTEDVALUE('Table'[WorkOrder])
var _CurValWorkDateTime = SELECTEDVALUE('Table'[WorkDateTime])
var _PrevValStep = Calculate ( Max('Table'[NewStep]), topn(1, 
                                              filter ( ALLSELECTED('Table'), 'Table'[WorkOrder] = _CurValWorkOrder && 'Table'[WorkDateTime] < _CurValWorkDateTime), 'Table'[WorkDateTime], desc))

Return _PrevValStep

Prev Step WorkDateTime check = 

var _CurValWorkOrder = SELECTEDVALUE('Table'[WorkOrder])
var _CurValWorkDateTime = SELECTEDVALUE('Table'[WorkDateTime])
var _PrevValWorkDateTime = Calculate ( Max('Table'[WorkDateTime]), topn(1, 
                                              filter ( ALLSELECTED('Table'), 'Table'[WorkOrder] = _CurValWorkOrder && 'Table'[WorkDateTime] < _CurValWorkDateTime), 'Table'[WorkDateTime], desc))

Return _PrevValWorkDateTime

Note: If you are creating this measure also, then use this measure in the Elapsed time dax measure.

Hope this helps!