Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello.
I have Fact table with time column (IssueTime).
I want to mesure the minutes between "violations" by order (first,second..)
The table include also a date column (IssueDate - 10/02/15,11/02/15...).
Ive tried Index column, datediff func and etc. but it didnt work.
example- 10/02/2015 | 08:39 AM | 09:13 AM | 34 MIN
10/02/2015 | 09:13 AM | 09:42 AM | 29 MIN
.
.
11/02/2015 | 05:10 AM | 05:12 AM | 2 MIN
(In the end ill check the avg minutes between ticket and ticket).
THNKS!
@Tim34 - If I understand what you are trying to do, it looks like you are trying to compare rows. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Basically, what you want to do is this:
Column = DATEDIFF([IssueTime],MINX(FILTER('Table',...),[Issue Time]),MINUTE)
Now, what exactly goes into the ... for your filter is up to you. I imagine that there is something that you want to group these on like Date Issued perhaps? Or some other identifying criteria.
Hi,
It doesnt work, that what it shows -
Time Issue | Time diff |
12:00 AM | 0 |
12:01 AM | -1319 |
12:02 AM | -2512 |
As you said I Want to compare rows and filter by Issuedate. look like that-
Issue date | Issue Time | PREVIOUS Row | Datediff |
01/02/15 | 12:00 AM | - | - |
01/02/15 | 12:01 AM | 12:00 AM | 1 MIN |
01/02/15 | 12:04 AM | 12:01 AM | 3 MIN |
02/02/15 | 06:00 AM | - | - |
02/02/15 | 06:13 AM | 06:00 AM | 13 MIN |
Hope you understand.
Thnks.
Hi @Tim34 ,
Create Calculated Columns
RANKDATE =
RANKX (
FILTER (
'Table',
'Table'[Issue date]
= EARLIER ( 'Table'[Issue date] )
),
CONCATENATE (
'Table'[Issue date],
'Table'[Issue Time]
),
,
ASC,
DENSE
)
Prev_Row =
CALCULATE (
MAX ( 'Table'[Issue Time] ),
FILTER (
'Table',
'Table'[Issue date]
= EARLIER ( 'Table'[Issue date] )
&& 'Table'[RANKDATE]
= EARLIER ( 'Table'[RANKDATE] ) - 1
)
)
Difference = DATEDIFF('Table'[Prev_Row],'Table'[Issue Time],MINUTE)
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
@Tim34 , the second column is not visible
usually it
Date diff = datediff([Date Time 1],[Date Time 2],MINUTE)
But if there dates and time in a separate table and it goes across Days, then like
Date diff = datediff([Date1]+ [Time 1],[Date 2]+ [Time 2],MINUTE)
Are you looking to generate your last example column, with the minutes between two time columns? If so, you can use the DATEDIFF() function as follows
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
between two rows, not columns-
in the same column Issuetime, the previous row.
User | Count |
---|---|
20 | |
18 | |
17 | |
11 | |
7 |
User | Count |
---|---|
28 | |
27 | |
13 | |
12 | |
12 |