Find previous event in an event log using DAX

[magnus_b]

Hi,

I have data like this:

case_id	timestamp	event
99288	28.10.2020 08:55:32	created
71263	28.10.2020 09:28:32	created
99288	28.10.2020 09:29:00	assigned
99288	28.10.2020 09:39:04	solved
71263	28.10.2020 10:44:02	solved
87100	28.10.2020 10:55:01	created
71263	28.10.2020 11:22:46	reopened

I want to calculate a new column that contains the previos event for the same case id:

case_id	timestamp	event	previous_event
99288	28.10.2020 08:55:32	created
71263	28.10.2020 09:28:32	created
99288	28.10.2020 09:29:00	assigned	created
99288	28.10.2020 09:39:04	solved	assigned
71263	28.10.2020 10:44:02	solved	created
87100	28.10.2020 10:55:01	created
71263	28.10.2020 11:22:46	reopened	solved

How can I achieve this using DAX?

[magnus_b]

@Greg_Deckler I have a more specific definition of what we are after, that might be easier to create a compatible measure to calculate. What we are really after, is the count of case_id that left the "assigned" state today (i.e. the previous_event = "assigned"). We cannot simply count the case_id that entered the "solved" state, because in reality our data is more complex and that won't give us the correct results.

[magnus_b]

@Greg_Deckler Hi again! I met another challenge, when trying to acheive the same in a source set up with direct query. The MAXX function is not allowed for direct query sources. Do you have a suggestion for how to solve that? Thank you so much for your help!

[Greg_Deckler · Accepted solution]

@magnus_b - Something like this should work:

Column = 
  VAR __PreviousTime = MAXX(FILTER('Table',[case_id] = EARLIER([case_id]) && [timestamp] 
 < EARLIER([timestamp])),[timestamp])
  VAR __PreviousState = MAXX(FILTER('Table',[case_id]=EARLIER([case_id]) && [timestamp]=__PreviousTime),[event])
RETURN
  __PreviousState

[magnus_b]

@Greg_Deckler That worked beautifully! Thank you!