Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I have one issue with a datediff between rows, based on an index and a filter.
I want to check the time between 2 status transactions.
I use the following DAX:
my output is as follow:
Now I only want to check the DateDiff between those rows where the UserFieldName = Status. I did it with the following change in the DAX:
still not the wanted output because my final row does not contains a value (this is logical given my DAX).
What options do I have to validate the time between index 1 and index 4 (so those items with UserFieldName =Status).
thanks in advance for your comment!
Solved! Go to Solution.
Hi @joep78 ,
Please update the formula of related calculated column as below and check if it can get your desired result:
Dif between previous index =
VAR _premDate =
CALCULATE (
MAX ( 'OrderHistory'[ModificationDateTime] ),
FILTER (
'OrderHistory',
OrderHistory[OrderRef] = EARLIER ( 'OrderHistory'[OrderRef] )
&& 'OrderHistory'[UserFieldName] = "Status"
&& 'OrderHistory'[UserFieldName] = EARLIER ( 'OrderHistory'[UserFieldName] )
&& 'OrderHistory'[Index] < EARLIER ( 'OrderHistory'[Index] )
)
)
RETURN
DATEDIFF ( _premDate, 'OrderHistory'[ModificationDateTime], MINUTE )
Best Regards
Rena
Hi @joep78 ,
Try simply to use a IF statment before the calculation redo your calculated column to:
Dif between previous index _ =
VAR currentOrder = [OrderRef]
VAR currentIndex = [Index]
VAR PreviousDatetime =
CALCULATE (
MIN ( OrderHistory[ModificationDateTime] );
FILTER (
'OrderHistory';
'OrderHistory'[OrderRef] = currentOrder
&& 'OrderHistory'[Index] = currentIndex - 1
)
)
RETURN
IF (
OrderHistory[UserFieldName] = "Status";
DATEDIFF ( [ModificationDateTime]; PreviousDatetime; MINUTE ) * -1;
BLANK ()
)
On my test return the correct result.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi Miguel,
Thanks for your reply. my output based on your solution(but another example with more addition rows) is as follow:
So now only those rows where UserFieldName = Status does contains a value, but the value in it is not based on the previous column where UserFieldName = Status but on the previous row. In this case this row should have a value of 1 (the previous row with UserFieldName = Status was 1 minute earlier). Does this make any sense?
Hi @joep78 ,
Please update the formula of related calculated column as below and check if it can get your desired result:
Dif between previous index =
VAR _premDate =
CALCULATE (
MAX ( 'OrderHistory'[ModificationDateTime] ),
FILTER (
'OrderHistory',
OrderHistory[OrderRef] = EARLIER ( 'OrderHistory'[OrderRef] )
&& 'OrderHistory'[UserFieldName] = "Status"
&& 'OrderHistory'[UserFieldName] = EARLIER ( 'OrderHistory'[UserFieldName] )
&& 'OrderHistory'[Index] < EARLIER ( 'OrderHistory'[Index] )
)
)
RETURN
DATEDIFF ( _premDate, 'OrderHistory'[ModificationDateTime], MINUTE )
Best Regards
Rena
Hi Rena & Miguel,
Thanks for your support, with the final adjustment of Rena my solution gives the correct output. Kudos for both of you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |