Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.