Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 ) )
Solved! Go to 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
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.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
82 | |
66 | |
61 | |
46 | |
45 |