Forum Discussion
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
- Greg_DecklerCommunity Champion
Juju123 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous - Juju123Helper III
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-zhangtiCommunity 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.
- Juju123Helper 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-zhangtiCommunity 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.
- Juju123Helper III
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 problemWorkflow Article Designation article Operation code Operation date WF00002 123456 STICK OPE020 05/11/2018 WF00002 123456 STICK OPE070 19/12/2018 WF00002 123456 STICK OPE090 15/11/2018 WF00002 123456 STICK OPE099 13/12/2018 WF00002 123456 STICK OPE110 07/12/2018 WF00002 123456 STICK OPE140 07/12/2018 WF00002 123456 STICK OPE150 07/12/2018 WF00002 741852 ON BOARD OPE020 05/11/2018 WF00002 741852 ON BOARD OPE070 19/12/2018 WF00002 741852 ON BOARD OPE090 15/11/2018 WF00002 741852 ON BOARD OPE099 13/12/2018 WF00002 741852 ON BOARD OPE110 07/12/2018 WF00002 741852 ON BOARD OPE140 07/12/2018 WF00002 741852 ON BOARD OPE150 07/12/2018 Thanks
- v-zhangtiCommunity Support
- Juju123Helper 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.
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 🙂