Forum Discussion

Juju123's avatar
Juju123
Icon for Helper III rankHelper 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 ...
  • v-zhangti's avatar
    v-zhangti
    2 years ago

    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.

v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
2 years ago

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
Icon for Helper III rankHelper III
2 years ago

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
    Icon for Community Support rankCommunity Support
    2 years ago

    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.

    • Juju123's avatar
      Juju123
      Icon for Helper III rankHelper III
      2 years ago

      v-zhangti ,

       

      Thanks you so much. 

      I'm beginner on Power BI and i want to understand. 

      How can I add the "Index" column? Is this a code where you have to go somewhere in the data transformations to add the "Index" column?

       

      2) Thank you very much for your 2 solutions, I will test them and get back to you. Thanks again