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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
icdns
Post Patron
Post Patron

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

@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
jdbuchanan71
Super User
Super User

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
    )

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! 🙂 

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

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.

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

Hi, @jdbuchanan71 

 

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

 

 

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! 😄

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.

Hi @jdbuchanan71 ,

 

Thank you for this again! 😄 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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