Difference between two rows with with multiple filters

[Anonymous]

I have the following table which has an Index, and a ServiceIndex (indexed against Name & Service):

Index	ServiceIndex	Name	Service	EnterOrExit	Time
0	1	John	Wash	Exit	10:00:00
1	2	John	Wash	Enter	10:30:00
2	3	John	Wash	Exit	11:00:00
3	1	John	Cut	Exit	10:15:00
4	2	John	Cut	Enter	10:36:22
5	3	John	Cut	Exit	10:38:11
6	4	John	Cut	Enter	10:40:44
7	5	John	Cut	Exit	10:50:22
8	1	Mike	Wash	Exit	10:00:00
9	2	Mike	Wash	Enter	10:25:22
10	3	Mike	Wash	Exit	10:28:11
11	4	Mike	Wash	Enter	10:33:21
12	5	Mike	Wash	Exit	10:38:22
13	1	Mike	Cut	Exit	10:05:08
14	1	Arthur	Trim	Exit	10:02:35
15	2	Arthur	Trim	Enter	10:09:06
16	3	Arthur	Trim	Exit	10:15:36
17	1	Arthur	Cut	Exit	10:23:25
18	2	Arthur	Cut	Enter	10:33:22
19	3	Arthur	Cut	Exit	10:40:01
20	4	Arthur	Cut	Enter	10:42:22
21	5	Arthur	Cut	Exit	10:44:00
22	6	Arthur	Cut	Enter	10:48:36
23	7	Arthur	Cut	Exit	11:02:02
24	8	Arthur	Cut	Enter	11:11:05

I want to add a calculated column, using DAX, to calculate the difference in time between the current ServiceIndex, and the previous one (When previous does not exist because ServiceIndex=1, then value should be null)

So my desired result for the above table sample would be:

Index	ServiceIndex	Name	Service	EnterOrExit	Time	TimeDifference
0	1	John	Wash	Exit	10:00:00
1	2	John	Wash	Enter	10:30:00	00:30:00
2	3	John	Wash	Exit	11:00:00	00:30:00
3	1	John	Cut	Exit	10:15:00
4	2	John	Cut	Enter	10:36:22	00:21:22
5	3	John	Cut	Exit	10:38:11	00:01:49
6	4	John	Cut	Enter	10:40:44	00:02:33
7	5	John	Cut	Exit	10:50:22	00:09:38
8	1	Mike	Wash	Exit	10:00:00
9	2	Mike	Wash	Enter	10:25:22	00:25:22
10	3	Mike	Wash	Exit	10:28:11	00:02:49
11	4	Mike	Wash	Enter	10:33:21	00:05:10
12	5	Mike	Wash	Exit	10:38:22	00:05:01
13	1	Mike	Cut	Exit	10:05:08
14	1	Arthur	Trim	Exit	10:02:35
15	2	Arthur	Trim	Enter	10:09:06	00:06:31
16	3	Arthur	Trim	Exit	10:15:36	00:06:30
17	1	Arthur	Cut	Exit	10:23:25
18	2	Arthur	Cut	Enter	10:33:22	00:09:57
19	3	Arthur	Cut	Exit	10:40:01	00:06:39
20	4	Arthur	Cut	Enter	10:42:22	00:02:21
21	5	Arthur	Cut	Exit	10:44:00	00:01:38
22	6	Arthur	Cut	Enter	10:48:36	00:04:36
23	7	Arthur	Cut	Exit	11:02:02	00:13:26
24	8	Arthur	Cut	Enter	11:11:05	00:09:03

Im quite new at Power BI & DEX, so any help is greatly appreciated, and I hope my question makes sense.

Is this possible?

So far I have only been able to do it against the Index column, which does not produce the desired results, changing the below to use the ServiceIndex gives me error A table of multiple values was supplied where a single value was expected. I understand why I am getting the error but unsure on what to change/add to get the desired result.

UpOrDownTime = 
VAR NextIndex = Event[Index] + 1
RETURN
    Event[Time]
        - CALCULATE (
            VALUES ( Event[Time] ),
            FILTER ( ALL ( Event ), Event[Index] = NextIndex )
        )

[Ashish_Mathur]

Hi,

This calculated column formula works

=if(ISBLANK(CALCULATE(MAX(Data[Time]),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Service]=EARLIER(Data[Service])&&Data[ServiceIndex]<EARLIER(Data[ServiceIndex])))),BLANK(),Data[Time]-CALCULATE(MAX(Data[Time]),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Service]=EARLIER(Data[Service])&&Data[ServiceIndex]<EARLIER(Data[ServiceIndex]))))

Hope this helps.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Try this DAX calculated Column:

Column 4 = VAR Index = 'Table'[Index]
VAR Reference = 'Table'[Service]
VAR Prevtime =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Time], TRUE () ),
        FILTER ( 'Table', 'Table'[Index] = Index - 1 && 'Table'[Service] = Reference )
    )
RETURN
    IF (
        ISBLANK ( Prevtime),
        blank(),
        'Table'[Time] - Prevtime
    )

Here is my output based on your screenshot:

Let me know if this works.

Thanks,

Tejaswi