Forum Discussion
Calculate the difference between each date
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.
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.
| Workflow | Article | Designation article | Operation code | Operation date | Previous date of precedent operation |
| WF00002 | 123456 | STICK | OPE020 | 05/11/2018 | |
| WF00002 | 123456 | STICK | OPE070 | 19/12/2018 | 05/11/2018 |
| WF00002 | 123456 | STICK | OPE090 | 15/11/2018 | 19/12/2018 |
| WF00002 | 123456 | STICK | OPE099 | 13/12/2018 | 15/11/2018 |
| WF00002 | 123456 | STICK | OPE110 | 07/12/2018 | 13/12/2018 |
| WF00002 | 123456 | STICK | OPE140 | 07/12/2018 | 07/12/2018 |
| WF00002 | 123456 | STICK | OPE150 | 07/12/2018 | 07/12/2018 |
| WF00002 | 741852 | ON BOARD | OPE020 | 05/11/2018 | |
| WF00002 | 741852 | ON BOARD | OPE070 | 19/12/2018 | 05/11/2018 |
| WF00002 | 741852 | ON BOARD | OPE090 | 15/11/2018 | 19/12/2018 |
| WF00002 | 741852 | ON BOARD | OPE099 | 13/12/2018 | 15/11/2018 |
| WF00002 | 741852 | ON BOARD | OPE110 | 07/12/2018 | 13/12/2018 |
| WF00002 | 741852 | ON BOARD | OPE140 | 07/12/2018 | 07/12/2018 |
| WF00002 | 741852 | ON BOARD | OPE150 | 07/12/2018 | 07/12/2018 |
Thanks 🙂
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.