Datediff - Minutes (DAX) help

[Tim34]

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!

[Greg_Deckler]

@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.

[Tim34]

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.

[harshnathani]

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)

[amitchandak]

@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)

[mahoneypat]

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

[Tim34]

between two rows, not columns-

in the same column Issuetime, the previous row.