topic Re: Datediff - Minutes (DAX) help in DAX Commands and Tips

Datediff - Minutes (DAX) help

Tim34 — Sun, 02 Aug 2020 21:38:43 GMT

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!

Re: Datediff - Minutes (DAX) help

mahoneypat — Sun, 02 Aug 2020 23:19:18 GMT

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

Delta = DATEDIFF(Times[Column1], Times[Column2], MINUTE)

If not, can you clarify further what you desired output looks like and the logic behind it.

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Datediff - Minutes (DAX) help

amitchandak — Mon, 03 Aug 2020 03:16:31 GMT

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)

Re: Datediff - Minutes (DAX) help

Greg_Deckler — Mon, 03 Aug 2020 03:54:47 GMT

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/339586

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.

Re: Datediff - Minutes (DAX) help

Tim34 — Mon, 03 Aug 2020 10:01:29 GMT

Hi,

It doesnt work, that what it shows -

Time diff = DATEDIFF(FactParkingViolation[IssueTime],MINX(FILTER(FactParkingViolation,FactParkingViolation[IssueDate]),FactParkingViolation[IssueTime]),MINUTE)

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.

Re: Datediff - Minutes (DAX) help

Tim34 — Mon, 03 Aug 2020 10:03:10 GMT

between two rows, not columns-

in the same column Issuetime, the previous row.

Re: Datediff - Minutes (DAX) help

Anonymous — Tue, 04 Aug 2020 09:07:11 GMT

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)