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

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

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

    Hi, Juju123 

     

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

     

    Best Regards

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

      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 🙂

      • v-zhangti's avatar
        v-zhangti
        Icon for Community Support rankCommunity Support
        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.