Forum Discussion
Calculated Column - time between events in different rows
Hi jpurcell
These Calculated Columns will also give you desired results
First an Index Column
Index = IF ( OR ( TableName[EventType] = "Save", TableName[EventType] = "Open" ), RANKX ( FILTER ( ALL ( TableName ), OR ( TableName[EventType] = "Save", TableName[EventType] = "Open" ) && TableName[UserID] = EARLIER ( TableName[UserID] ) ), TableName[DateTime], , asc ) )Now the TimeSinceLastSave
Time since last save = VAR Previoustime = CALCULATE ( VALUES ( TableName[DateTime] ), FILTER ( ALLEXCEPT ( TableName, TableName[UserID] ), TableName[Index] = EARLIER ( TableName[Index] ) - 1 ) ) RETURN IF ( NOT ( ISBLANK ( TableName[Index] ) ) && TableName[EventType] = "Save", DATEDIFF ( Previoustime, TableName[DateTime], MINUTE ) )
Hi jpurcell
I think this calculated table might be close
New Table =
VAR SelfJoin =
FILTER(
CROSSJOIN(
'Events',
SELECTCOLUMNS(
FILTER('Events', [EventType] in {"Save","Open"}),
"xUserID",[UserID],
"xDateTime",[DateTime]
)
),
[xDateTime] < [DateTime] &&
[UserID] = [xUserID]
)
VAR LastPurchases =
GROUPBY(
SelfJoin,
'Events'[UserID],
Events[DateTime],
"Last DateTime",MAXX(
CURRENTGROUP(),
[xDateTime]
)
)
RETURN
ADDCOLUMNS(
NATURALLEFTOUTERJOIN('Events', LastPurchases),
"Days since last purchase" , IF('Events'[EventType]="Save" , DATEDIFF([Last DateTime],[DateTime],MINUTE),BLANK())
)Hi jpurcell
These Calculated Columns will also give you desired results
First an Index Column
Index =
IF (
OR ( TableName[EventType] = "Save", TableName[EventType] = "Open" ),
RANKX (
FILTER (
ALL ( TableName ),
OR ( TableName[EventType] = "Save", TableName[EventType] = "Open" )
&& TableName[UserID] = EARLIER ( TableName[UserID] )
),
TableName[DateTime],
,
asc
)
)Now the TimeSinceLastSave
Time since last save =
VAR Previoustime =
CALCULATE (
VALUES ( TableName[DateTime] ),
FILTER (
ALLEXCEPT ( TableName, TableName[UserID] ),
TableName[Index]
= EARLIER ( TableName[Index] ) - 1
)
)
RETURN
IF (
NOT ( ISBLANK ( TableName[Index] ) )
&& TableName[EventType] = "Save",
DATEDIFF ( Previoustime, TableName[DateTime], MINUTE )
)That was quite ingenious .....Champ Phil_Seamark :smileyhappy:
Can I have this results only using measures?
Hi all
I tried this formula on my situation because it looks like the same goal, but when adapting the formula it only works partly. The strange thing is that once in a while it actually calculates the time between the events. (in the table shown below It only calculated the last event) .
My situation is show below. I want to calculate the time between when an event occurs ‘’Gekomen’’ and when the events disappears ‘’Gegaan’’, shown in the Status row. The Events have an ID which is connected by an type of event, so it can occur multiple times, I want to know the time between the status (gegaan & gekomen). (the difficult part is that all the values are appearing trough each other and the events can appear and disappear at the same time, also the events can happen multiple times in a short time.
I hope you can help me!
Status Date and Time ID Index Duration
Gegaan 1-2-2021 09:34 10 7
Gegaan 1-2-2021 09:34 637 7
Gegaan 1-2-2021 09:34 641 3
Gegaan 1-2-2021 09:34 641 3
Gegaan 1-2-2021 09:35 637 9
Gegaan 1-2-2021 09:35 10 9
Gegaan 1-2-2021 09:37 635 1
Gegaan 1-2-2021 09:37 626 3
Gekomen 1-2-2021 09:37 635 1
Gekomen 1-2-2021 09:37 635 1
Gegaan 1-2-2021 09:37 635 1
Gegaan 1-2-2021 09:37 626 3
Gekomen 1-2-2021 09:37 655 1
Gegaan 1-2-2021 09:37 655 1
Gekomen 1-2-2021 09:38 656 1
Gegaan 1-2-2021 09:39 656 2 1
Hello there,
I have a similar issue where I have several colmns but I just need time difference from the same colmn/EventDt time based of different colmn that has OrderStatus. Kindly help!! I am really new to power BI, dont use DAX a lot.My table looks like below.
Order ID/ EventDt Time/ OrderStatus. each is a colmn.
for a single order at different timings the status changed from recieved to filled to packaged to shipped. I need the average and also the time difference. Please feel free to ask any questions for clarification.
Hi!, im using your method:
The first column works fine
but then this error comes in the other one:
"a circular dependency was detected"
hope you have an answer to this :(, thanks!!
