locked
How much time from the last intervention RRS feed

  • 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).

    Thank you in advance ! 

    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

Answers

  • Hi CatalinAndrei,

    Thanks for your question.

    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,

    Thanks for your question.

    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