Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Difference between two rows with with multiple filters

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

 

IndexServiceIndexNameServiceEnterOrExitTime
01JohnWashExit10:00:00
12JohnWashEnter10:30:00
23JohnWashExit11:00:00
31JohnCutExit10:15:00
42JohnCutEnter10:36:22
53JohnCutExit10:38:11
64JohnCutEnter10:40:44
75JohnCutExit10:50:22
81MikeWashExit10:00:00
92MikeWashEnter10:25:22
103MikeWashExit10:28:11
114MikeWashEnter10:33:21
125MikeWashExit10:38:22
131MikeCutExit10:05:08
141ArthurTrimExit10:02:35
152ArthurTrimEnter10:09:06
163ArthurTrimExit10:15:36
171ArthurCutExit10:23:25
182ArthurCutEnter10:33:22
193ArthurCutExit10:40:01
204ArthurCutEnter10:42:22
215ArthurCutExit10:44:00
226ArthurCutEnter10:48:36
237ArthurCutExit11:02:02
248ArthurCutEnter11: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:

IndexServiceIndexNameServiceEnterOrExitTimeTimeDifference
01JohnWashExit10:00:00 
12JohnWashEnter10:30:0000:30:00
23JohnWashExit11:00:0000:30:00
31JohnCutExit10:15:00 
42JohnCutEnter10:36:2200:21:22
53JohnCutExit10:38:1100:01:49
64JohnCutEnter10:40:4400:02:33
75JohnCutExit10:50:2200:09:38
81MikeWashExit10:00:00 
92MikeWashEnter10:25:2200:25:22
103MikeWashExit10:28:1100:02:49
114MikeWashEnter10:33:2100:05:10
125MikeWashExit10:38:2200:05:01
131MikeCutExit10:05:08 
141ArthurTrimExit10:02:35 
152ArthurTrimEnter10:09:0600:06:31
163ArthurTrimExit10:15:3600:06:30
171ArthurCutExit10:23:25 
182ArthurCutEnter10:33:2200:09:57
193ArthurCutExit10:40:0100:06:39
204ArthurCutEnter10:42:2200:02:21
215ArthurCutExit10:44:0000:01:38
226ArthurCutEnter10:48:3600:04:36
237ArthurCutExit11:02:0200:13:26
248ArthurCutEnter11:11:0500: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 )
        )
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

Capture12.PNG

 

Let me know if this works.

Thanks,

Tejaswi

 

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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:

Capture12.PNG

 

Let me know if this works.

Thanks,

Tejaswi

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.