# How much time from the last intervention

• ### Question

• Hi

I have the following table for which I need to calculate how many days past from the last intervention .

﻿﻿Basically, for the TMS ID ATM00155 last intervention was closed on 02.03.2018 , and the next ticket ( which is a unique code and chronological , 318383) was on 16.03.2018. , and I want to show the result on a new column for row 7 , 16.03.18 minus 02.03.2018. For the row 6 in this new column the value should be 0(zero).

 Problem ID TMS ID Opening date Closing date           Result 317821 ATM00107 13.03.2018 13.03.2018               0 319454 ATM00136 27.03.2018 28.03.2018               0 319560 ATM00136 28.03.2018 28.03.2018               0 317340 ATM00139 07.03.2018 08.03.2018               0 316785 ATM00155 01.03.2018 02.03.2018              0 318383 ATM00155 16.03.2018 22.03.2018             14 319306 ATM00155 26.03.2018 28.03.2018               4

Friday, May 18, 2018 1:40 PM

• Hi CatalinAndrei,

In this scenario, please try to create a calculated column called MaxClosingDate as below:

```MaxClosingDate = CALCULATE(MAX(Problem[Closing Date]),
FILTER(Problem,
Problem[TMS ID]=EARLIER(Problem[TMS ID]) &&
Problem[Problem ID]<EARLIER(Problem[Problem ID]))
)```

Then you can try to create another column called Results as below:

```Results = If(ISBLANK(Problem[MaxClosingDate]),0,
DATEDIFF(Problem[MaxClosingDate],Problem[Opening Date],DAY))```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Monday, May 21, 2018 4:17 AM

### All replies

• Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
With PQ "M", not PP "DAX".
Time between Close and next Open for each TMS ID.
Count minutes between in "mm/dd/yy hh:mm" formats.
Sum intervening working hours only.
http://www.mediafire.com/file/41958a8m7aqs2rn/05_18_18.xlsx
http://www.mediafire.com/file/7sz2bt1tk06gc8z/05_18_18.pdf

Saturday, May 19, 2018 12:07 AM
• Hi CatalinAndrei,

In this scenario, please try to create a calculated column called MaxClosingDate as below:

```MaxClosingDate = CALCULATE(MAX(Problem[Closing Date]),
FILTER(Problem,
Problem[TMS ID]=EARLIER(Problem[TMS ID]) &&
Problem[Problem ID]<EARLIER(Problem[Problem ID]))
)```

Then you can try to create another column called Results as below:

```Results = If(ISBLANK(Problem[MaxClosingDate]),0,
DATEDIFF(Problem[MaxClosingDate],Problem[Opening Date],DAY))```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Monday, May 21, 2018 4:17 AM
• Guys , you are the best ! it's working just fine. Thank you very much.

PS I forgot to mention that I am a beginner

Monday, May 21, 2018 6:06 AM
• Thank you. I will try the power query as you suggested as well , and let you know.

BR

Catalin

Monday, May 21, 2018 6:07 AM