Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Have a data like below
Ticket num | Created |
Ticket_1 | 11/3/2022 0:06 |
Ticket_1 | 11/3/2022 0:18 |
Ticket_1 | 11/3/2022 0:21 |
Ticket_1 | 11/3/2022 0:22 |
Ticket_1 | 11/3/2022 0:23 |
Ticket_1 | 11/3/2022 0:24 |
Ticket_1 | 11/3/2022 0:26 |
Ticket_2 | 12/12/2022 13:40 |
Ticket_2 | 12/12/2022 13:41 |
Ticket_2 | 12/12/2022 13:43 |
Ticket_2 | 12/12/2022 13:45 |
Ticket_2 | 12/12/2022 13:45 |
Ticket_2 | 12/12/2022 13:46 |
Would like to calculate Time Taken in each ticket by subtracting the created column data for each row for all ticket num.
Here Time taken for 1st row calculated as ( 11/3/2022 0:18 - 11/3/2022 0:06) *24 = 0.1919444 ....until another ticket starts, to have 0 before a new ticket starts
Results to look something like below table
Ticket num | Created | Time Taken |
Ticket_1 | 11/3/2022 0:06 | 0.1919444 |
Ticket_1 | 11/3/2022 0:18 | 0.0547222 |
Ticket_1 | 11/3/2022 0:21 | 0.0175 |
Ticket_1 | 11/3/2022 0:22 | 0.0108333 |
Ticket_1 | 11/3/2022 0:23 | 0.0166667 |
Ticket_1 | 11/3/2022 0:24 | 0.0369444 |
Ticket_1 | 11/3/2022 0:26 | 0 |
Ticket_2 | 12/12/2022 13:40 | 0.0158333 |
Ticket_2 | 12/12/2022 13:41 | 0.0205556 |
Ticket_2 | 12/12/2022 13:43 | 0.0419444 |
Ticket_2 | 12/12/2022 13:45 | 0.0041667 |
Ticket_2 | 12/12/2022 13:45 | 0.0061111 |
Ticket_2 | 12/12/2022 13:46 | 0
|
Solved! Go to Solution.
Hi,
I am not sure if I understodd your question correctly, but I tried to create a sample pbix file like below.
It is for creating a new column, and the result is described in Second.
Please check the below picture and the attached pbix file.
OFFSET function (DAX) - DAX | Microsoft Learn
COALESCE function (DAX) - DAX | Microsoft Learn
expected reuslt CC =
VAR _currentdatetime = Data[Created]
VAR _nextdatetime =
MAXX (
OFFSET (
1,
SUMMARIZE ( Data, Data[Ticket num], Data[Created] ),
ORDERBY ( Data[Created], ASC ),
,
PARTITIONBY ( Data[Ticket num] )
),
Data[Created]
)
RETURN
COALESCE ( DATEDIFF ( _currentdatetime, _nextdatetime, SECOND ), 0 )
Hi,
I am not sure if I understodd your question correctly, but I tried to create a sample pbix file like below.
It is for creating a new column, and the result is described in Second.
Please check the below picture and the attached pbix file.
OFFSET function (DAX) - DAX | Microsoft Learn
COALESCE function (DAX) - DAX | Microsoft Learn
expected reuslt CC =
VAR _currentdatetime = Data[Created]
VAR _nextdatetime =
MAXX (
OFFSET (
1,
SUMMARIZE ( Data, Data[Ticket num], Data[Created] ),
ORDERBY ( Data[Created], ASC ),
,
PARTITIONBY ( Data[Ticket num] )
),
Data[Created]
)
RETURN
COALESCE ( DATEDIFF ( _currentdatetime, _nextdatetime, SECOND ), 0 )
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |