Forum Discussion

Juju123's avatar
Juju123
Helper III
2 years ago
Solved

Calculate the difference between each date

Hello, 

I just started on Power BI.

I have a table in which I have several columns:

I want to calculate the time between each operation:
- In yellow, this is the calculation that I wish to carry out in Power BI

- In green, this is the result that the calculation should return to me. The number of days between each operation.

 

Do you have any ideas on how to proceed?

Thanks you in advance for your help

  • Hi, Juju123 

     

    This result you expect still requires the addition of the index column. But considering that you are in direct query mode, you can't add it in Power query kind of thing. is the operation code in sequential order?

    Measure:

    Code index = VALUE(RIGHT(SELECTEDVALUE('Table'[Operation code]),3))
    Previous code index = 
    VAR _index = [Code index]
    RETURN
        MAXX ( FILTER ( ALL ( 'Table' ),
                [Code index] < _index
                    && [Article] = SELECTEDVALUE ( 'Table'[Article] )
                    && [Workflow] = SELECTEDVALUE ( 'Table'[Workflow] )
                    && [Designation article] = SELECTEDVALUE ( 'Table'[Designation article] )
            ),
            [Code index]
        )
    Previous date = 
    Var _preindex=[Previous code index]
    Return
    MAXX(FILTER(ALL('Table'),[Code index]=_preindex
                    && [Article] = SELECTEDVALUE ( 'Table'[Article] )
                    && [Workflow] = SELECTEDVALUE ( 'Table'[Workflow] )
                    && [Designation article] = SELECTEDVALUE ( 'Table'[Designation article] )
    
    ),[Operation date])

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

14 Replies

  • Hi Greg_Deckler , 

    thanks for your answer. 
    i read your post about MBT and I take your code. 

    i would like understand , what does [value] correspond to ?

    i modify the code like this : 

    Time each operation = 
      VAR _Current = [Value] ?
      VAR _PreviousDate = MAXX(FILTER('T1','Table'[Operation date] < EARLIER('T1'[Operation date])),[Operation date])

      VAR _Previous = MAXX(FILTER('T1',[Operation date]= VAR _PreviousDate),[Value])
    RETURN
    VAR _Current  - VAR _Previous


    thanks you 

  • v-zhangti's avatar
    v-zhangti
    Community Support

    Hi, Juju123 

     

    You can try the following methods.
    Column:

    Previous date = MAXX(FILTER('Table',[Date operation]<EARLIER('Table'[Date operation])),[Date operation])
    Result = IF([Previous date]=BLANK(),0,[Date operation]-[Previous date])

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Juju123's avatar
      Juju123
      Helper III

      Hi v-zhangti ,

       

      Thanks you for your answer.

       

      The result I want is indeed the one you posted, however regarding my data, I forgot to specify that the order of operations is different from that of the dates.

       

      That is to say that I can have an operation 10 which takes place on 05/11/2023 and an operation 20 which takes place on 09/09/2023. So I don't know if your formula works with dates that are not "sorted" in order

       

      Here is a sample of my data in CSV format : 

      Workflow number;Article;Designation article;Operation code;Operation label;Operation date
      WF0000000002;123456;STICK;OPE020;;05/11/2018
      WF0000000002;123456;STICK;OPE070;;19/12/2018
      WF0000000002;123456;STICK;OPE090;;15/11/2018
      WF0000000002;123456;STICK;OPE099;;13/12/2018
      WF0000000002;123456;STICK;OPE110;;07/12/2018
      WF0000000002;123456;STICK;OPE140;;07/12/2018
      WF0000000002;123456;STICK;OPE150;;07/12/2018
      WF0000000002;741852;ON BOARD;OPE020;;01/03/2018
      WF0000000002;741852;ON BOARD;OPE070;;05/06/2018
      WF0000000002;741852;ON BOARD;OPE090;;10/09/2018
      WF0000000002;741852;ON BOARD;OPE099;;13/09/2018
      WF0000000002;741852;ON BOARD;OPE110;;20/09/2018
      WF0000000002;741852;ON BOARD;OPE140;;25/10/2018
      WF0000000002;741852;ON BOARD;OPE150;;30/10/2018
      WF0000000002;741852;ON BOARD;OPE160;;05/11/2018

      • v-zhangti's avatar
        v-zhangti
        Community Support

        Hi, Juju123 

         

        There are two scenarios, so check to see if either is your desired outcome.

        The first scenario, add index column in the Power Query:

        Column:

        Previous date1 = Var _previousindex=MAXX(FILTER('Table',[Index]<EARLIER('Table'[Index])&&[Article]=EARLIER('Table'[Article])),[Index])
        Return
        CALCULATE(MAX('Table'[Operation date]),FILTER('Table',[Index]=_previousindex&&[Article]=EARLIER('Table'[Article])))
        Result1 = IF([Previous date1]=BLANK(),0,[Operation date]-[Previous date1])

        The second scenario:

        Column:

        Previous date2 = MAXX(FILTER('Table',[Operation date]<EARLIER('Table'[Operation date])&&[Article]=EARLIER('Table'[Article])),[Operation date])
        Result2 = IF([Previous date2]=BLANK(),0,[Operation date]-[Previous date2])

        If it fails to solve your problem, provide the desired output.

         

        Best Regards,

        Community Support Team _Charlotte

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-zhangti

    I added a column which is the operation number.

    I modified this formula to retrieve the previous date for each Workflow/Article pair:

     

    Previous_Date = MAXX(FILTER(ALL('CAGC_ADVL_INDICATEURS_ETAPES_WF_REP'),CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Numero de l'operation]<SELECTEDVALUE('CAGC_ADVL_INDICATEURS_ETAPES_WF_REP'[Numero de l'operation])&&[Designation article]=SELECTEDVALUE('CAGC_ADVL_INDICATEURS_ETAPES_WF_REP'[Designation article])),CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[WF - Date fin reelle])


    I can recover the previous date but not always.
    How can I fix this problem

     

    WorkflowArticleDesignation articleOperation codeOperation date
    WF00002123456STICKOPE02005/11/2018
    WF00002123456STICKOPE07019/12/2018
    WF00002123456STICKOPE09015/11/2018
    WF00002123456STICKOPE09913/12/2018
    WF00002123456STICKOPE11007/12/2018
    WF00002123456STICKOPE14007/12/2018
    WF00002123456STICKOPE15007/12/2018
    WF00002741852ON BOARDOPE02005/11/2018
    WF00002741852ON BOARDOPE07019/12/2018
    WF00002741852ON BOARDOPE09015/11/2018
    WF00002741852ON BOARDOPE09913/12/2018
    WF00002741852ON BOARDOPE11007/12/2018
    WF00002741852ON BOARDOPE14007/12/2018
    WF00002741852ON BOARDOPE15007/12/2018

    Thanks

    • v-zhangti's avatar
      v-zhangti
      Community Support

      Hi, Juju123 

       

      Based on your newly provided data, what kind of output do you expect?

       

      Best Regards

      • Juju123's avatar
        Juju123
        Helper III

        Hi v-zhangti ,

        Compared to the data I provided, I would like to retrieve the date of the previous operation for WORKFLOW/ARTICLE/DESIGNATION/ARTICLE/
        to then do the following calculation:
        Operation date - Previous date of precedent operation.

         

        The "Previous date of previous operation" column is the result I expect.

        However I am in DirectQuery mode so there are several functions that I cannot use.

        WorkflowArticleDesignation articleOperation codeOperation datePrevious date of precedent operation 
        WF00002123456STICKOPE02005/11/2018 
        WF00002123456STICKOPE07019/12/201805/11/2018
        WF00002123456STICKOPE09015/11/201819/12/2018
        WF00002123456STICKOPE09913/12/201815/11/2018
        WF00002123456STICKOPE11007/12/201813/12/2018
        WF00002123456STICKOPE14007/12/201807/12/2018
        WF00002123456STICKOPE15007/12/201807/12/2018
        WF00002741852ON BOARDOPE02005/11/2018 
        WF00002741852ON BOARDOPE07019/12/201805/11/2018
        WF00002741852ON BOARDOPE09015/11/201819/12/2018
        WF00002741852ON BOARDOPE09913/12/201815/11/2018
        WF00002741852ON BOARDOPE11007/12/201813/12/2018
        WF00002741852ON BOARDOPE14007/12/201807/12/2018
        WF00002741852ON BOARDOPE15007/12/201807/12/2018

         

        Thanks 🙂