Calculate the difference between each date

[Juju123]

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

[Juju123]

@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

Workflow	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-zhangti]

Hi, @Juju123 

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

Best Regards

[Juju123]

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 🙂

[v-zhangti · Accepted solution]

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]

@v-zhangti, 

Thank you so much !

I searched for several days but I couldn't find any solutions that worked in DirectQuery mode.

Your solution works perfectly, thank you again!!! 🙂

[v-zhangti]

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]

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]

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]

@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

[v-zhangti]

Hi, @Juju123 

Add index column:
Step1:

Step2:

Add an index column - Power Query | Microsoft Learn

Best Regards

[Juju123]

 @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]

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.

[Juju123]

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 

[Greg_Deckler]

@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/3395....
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