cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ziyabikram96
Helper V
Helper V

Time Difference between consecutive rows

I am getting error when ever consective c/in or C/out occure in calaculating time difference but whenever consecutive occure I want to pick maximum from the C/out and minimum from C/In for further clarification I am attaching a screenshot please help me in this 

Thank you

Thank you attendence.PNG

1 ACCEPTED SOLUTION

Hi, @ziyabikram96 

 

To create a calculated column like this:

isIN_is0 =
VAR _previouIn =
    CALCULATE (
        MIN ( [Index] ),
        FILTER (
            ALL ( 'Table' ),
            [State] = "C/In"
                && [Index]
                    = EARLIER ( [Index] ) - 1
        )
    )
VAR _if =
    IF ( 'Table'[State] = "C/In", IF ( [Index] - _previouIn = 1, 1 ) )
RETURN
    _if

then the test3 would be like this:

test_3 = 
VAR _CurrentIndex =
    FIRSTNONBLANK ('Table'[Index], 1 )
VAR _CurrentStatus =
    FIRSTNONBLANK ( 'Table'[State], 1 )
VAR _IndexOfPreviousCheckIN =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            AND ( 'Table'[Index] < _CurrentIndex, 'Table'[State] = "C/In" )
        ) //,OR('InOutData(PAK)'[Index] < CurrentIndex,
        // 'InOutData(PAK)'[State] = "C/Out")
    )
//*****************************************************************************************
var _lastOut=
        CALCULATE(
        LASTNONBLANK('Table'[Index],MAX('Table'[State])="C/Out"),
        FILTER(ALL('Table'),AND ( 'Table'[Index] < _CurrentIndex, 'Table'[State] = "C/Out" ))
    )
//*****************************************************************************************
//*****************************************************************************************
var _firstIn=
    IF(MAX('Table'[State])="C/In",_lastOut+1)
//*****************************************************************************************
VAR _IndexOfFollowingCheckOut =
    IF ( 
        ISBLANK ( _IndexOfPreviousCheckIN ), 
        0, 
        // _IndexOfPreviousCheckIN+1
//*****************************************************************************************
        _lastOut
//*****************************************************************************************
    )
var _result=
        IF (
        OR(
//*****************************************************************************************
            OR ( _CurrentIndex = 0, _CurrentStatus = "C/Out" ),MAX('Table'[isIN_is0])=1),
//*****************************************************************************************
        0,
        DATEDIFF (
            CALCULATE (
                FIRSTNONBLANK ( 'Table'[Date & Time State], 0 ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Index] = _IndexOfFollowingCheckOut
                )
            ),
            // FIRSTNONBLANK ( 'Table'[Date & Time State], 1 ),
//*****************************************************************************************
            CALCULATE(
                MAX('Table'[Date & Time State]),
                FILTER(
                    ALL('Table'),
                    'Table'[Index]=_firstIn
                )
            ),
//*****************************************************************************************
            MINUTE
        )
    )
RETURN _result

result:

vangzhengmsft_0-1628823716067.png

Please refer to the attachment below for details

 

 

Hope this helps.

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

9 REPLIES 9
v-angzheng-msft
Community Support
Community Support

Hi, @ziyabikram96 

 

I have made some modifications to the above formula:

test2 = 
VAR _CurrentIndex =
    FIRSTNONBLANK ('Table'[Index], 1 )
VAR _CurrentStatus =
    FIRSTNONBLANK ( 'Table'[State], 1 )
VAR _IndexOfPreviousCheckIN =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            AND ( 'Table'[Index] < _CurrentIndex, 'Table'[State] = "C/In" )
        ) //,OR('InOutData(PAK)'[Index] < CurrentIndex,
        // 'InOutData(PAK)'[State] = "C/Out")
    )
//*****************************************************************************************
var _lastOut=
        CALCULATE(
        LASTNONBLANK('Table'[Index],MAX('Table'[State])="C/Out"),
        FILTER(ALL('Table'),AND ( 'Table'[Index] < _CurrentIndex, 'Table'[State] = "C/Out" ))
    )
//*****************************************************************************************
VAR _IndexOfFollowingCheckOut =
    IF ( 
        ISBLANK ( _IndexOfPreviousCheckIN ), 
        0, 
        // _IndexOfPreviousCheckIN+1
//*****************************************************************************************
        _lastOut
//*****************************************************************************************
    )
var _result=
        IF (
        OR ( _CurrentIndex = 0, _CurrentStatus = "C/Out" ),
        0,
        DATEDIFF (
            CALCULATE (
                FIRSTNONBLANK ( 'Table'[Date & Time State], 0 ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Index] = _IndexOfFollowingCheckOut
                )
            ),
            FIRSTNONBLANK ( 'Table'[Date & Time State], 1 ),
            MINUTE
        )
    )
RETURN _result

Result:

vangzhengmsft_0-1628735829778.png

Please refer to the attachment below for details

 

 

Hope this helps.

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

and for more clarification i want difference between 

last_Out and First_In

Hi, @ziyabikram96 

 

To create a calculated column like this:

isIN_is0 =
VAR _previouIn =
    CALCULATE (
        MIN ( [Index] ),
        FILTER (
            ALL ( 'Table' ),
            [State] = "C/In"
                && [Index]
                    = EARLIER ( [Index] ) - 1
        )
    )
VAR _if =
    IF ( 'Table'[State] = "C/In", IF ( [Index] - _previouIn = 1, 1 ) )
RETURN
    _if

then the test3 would be like this:

test_3 = 
VAR _CurrentIndex =
    FIRSTNONBLANK ('Table'[Index], 1 )
VAR _CurrentStatus =
    FIRSTNONBLANK ( 'Table'[State], 1 )
VAR _IndexOfPreviousCheckIN =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            AND ( 'Table'[Index] < _CurrentIndex, 'Table'[State] = "C/In" )
        ) //,OR('InOutData(PAK)'[Index] < CurrentIndex,
        // 'InOutData(PAK)'[State] = "C/Out")
    )
//*****************************************************************************************
var _lastOut=
        CALCULATE(
        LASTNONBLANK('Table'[Index],MAX('Table'[State])="C/Out"),
        FILTER(ALL('Table'),AND ( 'Table'[Index] < _CurrentIndex, 'Table'[State] = "C/Out" ))
    )
//*****************************************************************************************
//*****************************************************************************************
var _firstIn=
    IF(MAX('Table'[State])="C/In",_lastOut+1)
//*****************************************************************************************
VAR _IndexOfFollowingCheckOut =
    IF ( 
        ISBLANK ( _IndexOfPreviousCheckIN ), 
        0, 
        // _IndexOfPreviousCheckIN+1
//*****************************************************************************************
        _lastOut
//*****************************************************************************************
    )
var _result=
        IF (
        OR(
//*****************************************************************************************
            OR ( _CurrentIndex = 0, _CurrentStatus = "C/Out" ),MAX('Table'[isIN_is0])=1),
//*****************************************************************************************
        0,
        DATEDIFF (
            CALCULATE (
                FIRSTNONBLANK ( 'Table'[Date & Time State], 0 ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Index] = _IndexOfFollowingCheckOut
                )
            ),
            // FIRSTNONBLANK ( 'Table'[Date & Time State], 1 ),
//*****************************************************************************************
            CALCULATE(
                MAX('Table'[Date & Time State]),
                FILTER(
                    ALL('Table'),
                    'Table'[Index]=_firstIn
                )
            ),
//*****************************************************************************************
            MINUTE
        )
    )
RETURN _result

result:

vangzhengmsft_0-1628823716067.png

Please refer to the attachment below for details

 

 

Hope this helps.

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Thanks for your time and kind responses I got my expected result 

ssd.PNG

Thanks for your time and response in C/Out logic is ok as it is highlited in yellow but the problem is occured in C/In as it is marked with black colour , I want minimum C/In and thier respective result and rest of it will return 0 

Greg_Deckler
Super User
Super User

@ziyabikram96 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Hi, Didn't get the expected result I tried your method but failed to to get it

sm_talha
Resolver II
Resolver II

Can you please show how are you calculating the time difference?

 

Here it is

test =
VAR CurrentIndex = FIRSTNONBLANK('InOutData(PAK)'[Index],1)
VAR CurrentStatus = FIRSTNONBLANK('InOutData(PAK)'[State],1)

VAR IndexOfPreviousCheckIN =
CALCULATE(
MAX('InOutData(PAK)'[Index]),
FILTER(
ALL('InOutData(PAK)'),
AND(
'InOutData(PAK)'[Index] < CurrentIndex ,
'InOutData(PAK)'[State] = "C/In"
)
 
 
)//,OR('InOutData(PAK)'[Index] < CurrentIndex,
// 'InOutData(PAK)'[State] = "C/Out")
)
VAR IndexOfFollowingCheckOut =
IF(
ISBLANK(IndexOfPreviousCheckIN),
0,
IndexOfPreviousCheckIN +1
)
RETURN
IF(
OR(CurrentIndex = 0, CurrentStatus = "C/Out" ),
0,


DATEDIFF(
CALCULATE(
FIRSTNONBLANK('InOutData(PAK)'[Time],0),
FILTER(
ALL('InOutData(PAK)'),
'InOutData(PAK)'[Index] = IndexOfFollowingCheckOut
)
),
FIRSTNONBLANK('InOutData(PAK)'[Time],1),
MINUTE
)
)

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors