Multiple IF taking in account previous rows

[AFra]

Hi, 

I have this table which is an export of a discussion between two persons.

I'd like to create a 4th column that will display A, B, C or D following these rules : 

- A : Time between Msg on Row N > .333

- B : Time between Msg on Row N < .000694 AND Author on Row N = Author on Row N-1

- C : Time between Msg on Row N < .333 AND Author on Row N <> Author on Row N-1

- D : Time between Msg on Row N+1 > 1

I see how easy this can be on excel. Here I guess I can use the EARLIER function to deal with the Row N / N-1 / N+1, but I don't fully get how to use it. 

So far I have : 

Type =

Type=if('2019'[Time Between Msg]>0,333;"A";if('2019'[Time Between Msg]<0,000694 && ??? 

Thanks in advance for any help! 

[v-lili6-msft · Accepted solution]

hi @AFra 

Try this way as below:

Step1:

Add an index column in table.

Step2:

Use this formula to get the expected output.

Result = 
VAR lastrow =
    CALCULATE (
        MAX ( 'Table'[Author] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
RETURN
    VAR nextrowTimebetweenMsg =
        CALCULATE (
            MAX ( 'Table'[Time Between Msg (days)] ),
            FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 )
        )
    RETURN
        IF (
            'Table'[Time Between Msg (days)] > 0.333,
            "A",
            IF (
                'Table'[Time Between Msg (days)] < 0.000694
                    && 'Table'[Author] = lastrow,
                "B",
                IF (
                    'Table'[Time Between Msg (days)] < 0.333
                        && 'Table'[Author] <> lastrow,
                    "C",
                    IF ( nextrowTimebetweenMsg > 1, "D" )
                )
            )
        )

and here is sample pbix file, please try it.

Regards,

Lin

[AFra]

it works nicely, thanks a lot!!! 

[Nathaniel_C]

Hi @AFra ,

If you would copy and paste this excel right into the post, or into the code panel (</> see the symbol above) we would be able to help you. BTW, earlier is not great, use a variable and a return.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[AFra]

Hi @Nathaniel_C , 

I'm not very familiar with the variables and returns neither 🙄

Here's the copy/paste of data

Date	Time Between Msg (days)	Author
1/1/19 2:00 AM	0,04066	XA
1/1/19 7:44 AM	0,23928	XA
1/1/19 12:05 PM	0,11520	GF
1/1/19 12:06 PM	0,00059	GF
1/1/19 1:12 PM	0,04571	XA
1/1/19 1:12 PM	0,00044	GF
1/1/19 3:37 PM	0,01402	GF
9/1/19 2:06 PM	7,93672	GF
10/1/19 1:41 AM	0,25485	GF
10/1/19 1:47 AM	0,00468	XA
10/1/19 6:27 AM	0,02689	GF
10/1/19 6:27 AM	0,00022	GF
12/1/19 1:31 PM	2,28171	XA
12/1/19 1:31 PM	0,00007	XA
19/1/19 5:19 PM	0,01395	XA
19/1/19 5:22 PM	0,00044	GF
19/1/19 6:29 PM	0,02322	GF
19/1/19 6:29 PM	0,00015	XA
19/1/19 6:52 PM	0,01575	GF
19/1/19 8:15 PM	0,02640	GF

Thanks a lot!!