Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Tim34
Frequent Visitor

Datediff - Minutes (DAX) help

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!

 

TIME .jpg

6 REPLIES 6
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi,

It doesnt work, that what it shows - 

Time diff = DATEDIFF(FactParkingViolation[IssueTime],MINX(FILTER(FactParkingViolation,FactParkingViolation[IssueDate]),FactParkingViolation[IssueTime]),MINUTE)
 
Time IssueTime diff
12:00 AM0
12:01 AM-1319
12:02 AM-2512

 

As you said I Want to compare rows and filter by Issuedate. look like that-

Issue dateIssue TimePREVIOUS RowDatediff
01/02/1512:00 AM--
01/02/1512:01 AM12:00 AM1 MIN
01/02/1512:04 AM12:01 AM3 MIN
02/02/1506:00 AM--
02/02/1506:13 AM06:00 AM13 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)

 

 

 

1.jpg

 

 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

amitchandak
Super User
Super User

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

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


between two rows, not columns-

 

in the same column Issuetime, the previous row.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.