Forum Discussion

Juju123's avatar
Juju123
Icon for Helper III rankHelper III
3 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
    3 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.

Juju123's avatar
Juju123
Icon for Helper III rankHelper III
3 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

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

     v-zhangti ,

     

    Thanks you. I try to add a column index but i'm in direct query and it's doesn't work, i have this message : This step generates a query that is not supported in DirectQuery mode

     

    I try the second solution but it's doesn't work in direct query. I have this error message for 2nd methode: 

    EARLIER/EARLIEST refers to an earlier row context that does not exist.

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

      Hi, Juju123 

       

      My example above is a calculated column. If yours is measure, you can change EARLIER to SELECTEDVALUE.

      Previous date measure = MAXX(FILTER(ALL('Table'),[Operation date]<SELECTEDVALUE('Table'[Operation date])&&[Article]=SELECTEDVALUE('Table'[Article])),[Operation date])
      Result measure = IF([Previous date measure]=BLANK(),0,SELECTEDVALUE('Table'[Operation date])-[Previous date measure])

       

       

      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.