Formula to show prior value of the element

[AmaliaD]

Hello,

I am reaching out on a matter that I could not fully solve, so I am hoping to find some help here.

The scenario:

-we have a team that covers multiple channels 

-all activities they handle are registered in the system - for each transaction they can select the channel the transaction came on

-multiple activities can belong to a case - so the same case can appear multiple times, but the activities on it can take place on multiple channels

-in the raw data I have an identifier for the case (so the same number can appear several times-depending how many activities are created within it) and a unique identifier for each activitiy within the case (one can appear only once)

-in the raw data , there is time stamp, showing when each activity was created

What I struggle with:

-I need a new column that would show the prior channel of the activity 

I know, in theory, what the synthax would need to include, yet I don't know exactly how to implement it. Basically, the formula needs to look at the case (which is the common denominator) + at the maximum timestamp which is right before the activity .

I tried with a combination of maxx, earlier, filter, but no success.

Below is how the raw data would look like. The columns Case, Activity, Channel and Date are within the raw data itself. The column "Prior channel" is what I need to create.

Any help would be much appreciated!

Thank you!

Amalia 

Case	Activity	Channel	Date	Prior channel
C1	C1-123	channel1	2/2/2023 10:25	-
C1	C1-124	channel1	2/2/2023 13:20	channel 1
C1	C1-125	channel2	2/3/2023 11:40	channel 1
C2	C2-234	channel2	2/4/2023 15:20	-
C2	C2-235	channel3	2/4/2023 17:10	channel 2
C2	C2-236	channel2	2/5/2023 9:24	channel 3

[Wilson_]

Hello Amalia,

Try the below as a new column. I also used EARLIER (kind of), FILTER and MAXX. 😄

Prior Channel = 
VAR CurrentDate = 'Cases'[Date]
VAR CurrentCase = 'Cases'[Case ]
VAR Filtered =
FILTER (
    'Cases',
    'Cases'[Case ] = CurrentCase && 'Cases'[Date] < CurrentDate
)

VAR MaxDateChannel =
MAXX (
    Filtered,
    'Cases'[Date] & 'Cases'[Channel] -- combining date and channel to return the max date and associated channel
)
VAR StartCharacter =  FIND ( "channel", MaxDateChannel, 1, 0) -- in case channel the text position of "channel" varies in MaxDateChannel
VAR Result = MID ( MaxDateChannel, StartCharacter, 99 )

RETURN Result

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[AmaliaD]

Hi Wilson, 

Thank you so much for taking the time to look into this. I applied a shorter version of the formula, as, if I understand correctly, the Find and subsequent part is meant to check for the word "channel", nonetheless, in the raw data, there will be the name of the actual channel and does not appear as I wrote in the example table 🙂 .

Hence, what I did was to show the result for:

MAXX (
    Filtered,
    'Cases'[Date] & 'Cases'[Channel] -- combining date and channel to return the max date and associated channel
)

 The output was helpful in checking the validity of the formula, as it captured not only the channel, but the date of the prior transaction. This way it was easy to see exactly which transaction it looks at and assesses as being the prior one. 

The formula is correct in about 50-60% of situations. I took several cases with multiple transactions and the output it accurate up to a point, yet it glitches somewhere. What it does, from a stage onward, is it tends to take the same prior transaction for several of the later instances. 

Continuing on the same example table, it behaves like this:

Case	Activity	Channel	Date	Prior channel
C1	C1-123	channel1	2/2/2023 10:25	-
C1	C1-124	channel1	2/2/2023 13:20	channel 1
C1	C1-125	channel2	2/3/2023 11:40	channel 1
C2	C2-234	channel2	2/4/2023 15:20	-
C2	C2-235	channel3	2/4/2023 17:10	channel 2
C2	C2-236	channel2	2/5/2023 9:24	channel 3
C2	C2-237	channel1	2/5/2023  12:30:00 PM	channel 3
C2	C2-238	channel1	2/5/2023  4:20:00 PM	channel 3
C2	C2-239	channel2	2/6/2023  2:40:00 PM	channel 3
C2	C2-240	channel3	2/7/2023  5:25:00 PM	channel 2

The thing is that those instances(C2-237, C2-238 and C2-239) that show channel 3 repeatedly (the ones marked in bold) actually do not read the transaction immediately prior to them. The channel is repeated because all those transactions read the same one (C2-235) as being prior to them. I can identify this thanks to the timestamp brought by the formula.

There are 2 things worth mentioning:

1-I noticed so far that this happens when 2 specific channels intertwine 

2-the formula can start reading things correctly within the same case after missing some transactions (so once it goes South, it does not necessarily stay there).

Could this be due to my shortening of the formula? 

Thank you once again so much!

Amalia