Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
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.
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:
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:
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
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
@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
Hi, Didn't get the expected result I tried your method but failed to to get it
Can you please show how are you calculating the time difference?
Here it is
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |