cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
icdns Frequent Visitor
Frequent Visitor

Subtract current row from latest date from last row

Hi Guys, 

 

Can you please help me, I would like to derive to this measure where in: 

 

Minimum Time before Failure = (Latest Ticket Created Time/Date)-(Previous Resolve Time/Date)

Note: Must be orderered by Resolve Time/Date. And Result must be on a Duration format like the "Duration" column. 

sample.png

 

This is a bit tricky as I am working on this also. Here's the dax that I'm working with:

 

Min_Time_Before_Failure = CALCULATE(MAX(raw_data_response[Resolve_Time_Date]),FILTER(raw_data_response,raw_data_response[Resolve_Time_Date] < EARLIER(raw_data_response[Resolve_Time_Date])))
 
Hope you can help me guys. Thank you in advance! 😄
 
 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Subtract current row from latest date from last row

@icdns 

Should only calls with the same priority be used when deterimining the previous resovled date and time?  If so we just need to read it for the current row the use it as a filter in the PreviousResolved VAR:

Miniumum Time Before Failure = 
VAR CurrentResolve = 'Table'[ResolveTime/Date]
VAR CurrentCreated = 'Table'[Ticket Created Time/Date]
VAR CurrentPriority = 'Table'[Call Priority]
VAR PreviouseResolved =
    CALCULATE( 
        MAX ( 'Table'[ResolveTime/Date] ),
        ALL ( 'Table' ),
        'Table'[ResolveTime/Date] < CurrentResolve,
        'Table'[Ticket Created Time/Date] < CurrentCreated,
        'Table'[Call Priority] = CurrentPriority
    )
VAR NumDays = DATEDIFF( PreviouseResolved,'Table'[Ticket Created Time/Date], DAY )
VAR DaysReturn =
    SWITCH (
        TRUE(),
        NumDays = 0, BLANK(),
        NumDays = 1, NumDays & " day ",
        NumDays > 1, NumDays & " days "
    )
RETURN 
DaysReturn & FORMAT (  'Table'[Ticket Created Time/Date] - PreviouseResolved,  "hh:mm:ss")

View solution in original post

9 REPLIES 9
Super User
Super User

Re: Subtract current row from latest date from last row

Hello @icdns 

Want to make sure I am understaing.  I put this measure into my sample as a calculated column.

Previous Resolve = 
VAR CurrentResolve = 'Table'[ResolveTime/Date]
RETURN 
    CALCULATE( 
        MAX ( 'Table'[ResolveTime/Date] ),
        ALL ( 'Table' ),
        'Table'[ResolveTime/Date] < CurrentResolve
    )

It pulls the highest Resolve Time/Date that is before my current rows Resolve Time/Date but it gets a little strange on ticket 2 becasue it was open the whole time that ticket 3 was opened and closed so the next lowest Resolve Time/Date is from Ticket 3.  We can fix it with another filter to only pull the highest resolve time/date from earlier tickets but wanted to make sure that is what you want.

TicketResolution.jpg

Previous Resolve 2 = 
VAR CurrentResolve = 'Table'[ResolveTime/Date]
VAR CurrentCreated = 'Table'[Ticket Created Time/Date]
RETURN 
    CALCULATE( 
        MAX ( 'Table'[ResolveTime/Date] ),
        ALL ( 'Table' ),
        'Table'[ResolveTime/Date] < CurrentResolve,
        'Table'[Ticket Created Time/Date] < CurrentCreated
    )
icdns Frequent Visitor
Frequent Visitor

Re: Subtract current row from latest date from last row

Hi jdbuchanan71

 

Thank you so muchhh for this! I haven't notice this kind of scenario.

 

The "Previous Resolved 2" is the correct logic. 😄 

 

Is it also possible that we can convert the result of Previous Resolved 2 = Latest (Ticket_Created_Time_Date) - Previous(Resolve_Time_Date) as duration? (Ex. 9 days 21:03:00 - DD HH:MM:SS) 

 

Thank you so much in advance! 🙂 

Super User
Super User

Re: Subtract current row from latest date from last row

Hello @icdns 

We can, yes.  Enter this as a calculated column in your table.

Miniumum Time Before Failure = 
VAR CurrentResolve = 'Table'[ResolveTime/Date]
VAR CurrentCreated = 'Table'[Ticket Created Time/Date]
VAR PreviouseResolved =
    CALCULATE( 
        MAX ( 'Table'[ResolveTime/Date] ),
        ALL ( 'Table' ),
        'Table'[ResolveTime/Date] < CurrentResolve,
        'Table'[Ticket Created Time/Date] < CurrentCreated
    )
VAR NumDays = DATEDIFF( PreviouseResolved,'Table'[Ticket Created Time/Date], DAY )
VAR DaysReturn =
    SWITCH (
        TRUE(),
        NumDays = 0, BLANK(),
        NumDays = 1, NumDays & " day ",
        NumDays > 1, NumDays & " days "
    )
RETURN 
DaysReturn & FORMAT (  'Table'[Ticket Created Time/Date] - PreviouseResolved,  "hh:mm:ss")

TicketResolutionSolved.jpg

icdns Frequent Visitor
Frequent Visitor

Re: Subtract current row from latest date from last row

Hi @jdbuchanan71 ,

 

I tried creating the calculated column given your dax. Minimum Time before failure is not showing correctly..

 

Is this because i have filter "Call Priority?" 

sample1.png

But when I created a table just for the "Priority 1" items, the correct values shows.. 

sample2.png

 

Thanks you.

Super User
Super User

Re: Subtract current row from latest date from last row

@icdns 

Should only calls with the same priority be used when deterimining the previous resovled date and time?  If so we just need to read it for the current row the use it as a filter in the PreviousResolved VAR:

Miniumum Time Before Failure = 
VAR CurrentResolve = 'Table'[ResolveTime/Date]
VAR CurrentCreated = 'Table'[Ticket Created Time/Date]
VAR CurrentPriority = 'Table'[Call Priority]
VAR PreviouseResolved =
    CALCULATE( 
        MAX ( 'Table'[ResolveTime/Date] ),
        ALL ( 'Table' ),
        'Table'[ResolveTime/Date] < CurrentResolve,
        'Table'[Ticket Created Time/Date] < CurrentCreated,
        'Table'[Call Priority] = CurrentPriority
    )
VAR NumDays = DATEDIFF( PreviouseResolved,'Table'[Ticket Created Time/Date], DAY )
VAR DaysReturn =
    SWITCH (
        TRUE(),
        NumDays = 0, BLANK(),
        NumDays = 1, NumDays & " day ",
        NumDays > 1, NumDays & " days "
    )
RETURN 
DaysReturn & FORMAT (  'Table'[Ticket Created Time/Date] - PreviouseResolved,  "hh:mm:ss")

View solution in original post

icdns Frequent Visitor
Frequent Visitor

Re: Subtract current row from latest date from last row

Hi, @jdbuchanan71 

 

It worked! Thank you so much for the help! 😄 

 

 

icdns Frequent Visitor
Frequent Visitor

Re: Subtract current row from latest date from last row

Hi, @jdbuchanan71 

Just a quick question, can I convert my "Minimum Time before Failure" column to number? Because I want to see its average..

Can I just simply use the modelling part view in PBI Desktop? 🙂 

 

Thank you! 😄

Super User
Super User

Re: Subtract current row from latest date from last row

You can, yes.  This will give you the number of minutes.

Miniumum Time Before Failure Minutes = 
VAR CurrentResolve = 'Table'[ResolveTime/Date]
VAR CurrentCreated = 'Table'[Ticket Created Time/Date]
VAR CurrentPriority = 'Table'[Call Priority]
VAR PreviouseResolved =
    CALCULATE( 
        MAX ( 'Table'[ResolveTime/Date] ),
        ALL ( 'Table' ),
        'Table'[ResolveTime/Date] < CurrentResolve,
        'Table'[Ticket Created Time/Date] < CurrentCreated,
        'Table'[Call Priority] = CurrentPriority
    )

VAR NumMinutes = DATEDIFF( PreviouseResolved,CurrentCreated, MINUTE )
RETURN 
    NumMinutes

I would say that you don't need to go down to the second when running an average over something that can span many days.

icdns Frequent Visitor
Frequent Visitor

Re: Subtract current row from latest date from last row

Hi @jdbuchanan71 ,

 

Thank you for this again! 😄 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 3,150 guests
Please welcome our newest community members: