Difference between two dates in two rows in a matrix

[_Andrew_]

Hi, I'm new to PowerBI and am having trouble calculating the difference between two rows in a matrix.

What I want is a matrix showing the earliest successful deployment and the interval between the first successful deployment for each version:

Version	Earliest Completed Date	Interval
2.12	19 June 2019
2.13	12 July 2019	23
3.0	10 August 2019	29
3.1	05 September 2019	26

I can easily get the first two columns, but can't come up with a measure to calculate the difference between the dates in neighbouring rows.

Is this possible in PowerBI?

Thanks.

Andrew.

A mockup of the raw data is something like this (obviously the actual data is more complicated, but this narrows it down to this exact question):

Unique Task ID	TaskState	Completed Date	Version
15428	Success	19/06/2019	2.12
15585	Success	22/06/2019	2.12
15726	Success	26/06/2019	2.12
15771	Success	27/06/2019	2.12
15773	Success	27/06/2019	2.12
15784	Success	27/06/2019	2.12
15841	Success	28/06/2019	2.12
15843	Success	28/06/2019	2.12
15886	Success	29/06/2019	2.12
15895	Success	29/06/2019	2.12
16131	Success	04/07/2019	2.12
16137	Failed	04/07/2019	2.12
16139	Failed	04/07/2019	2.12
16142	Success	04/07/2019	2.12
16145	Success	04/07/2019	2.12
16247	Success	06/07/2019	2.12
16280	Failed	06/07/2019	2.12
16281	Failed	06/07/2019	2.12
16282	Failed	06/07/2019	2.12
16284	Success	06/07/2019	2.12
16559	Success	12/07/2019	2.13
16611	Success	13/07/2019	2.13
16764	Success	17/07/2019	2.13
16846	Success	19/07/2019	2.13
16847	Success	19/07/2019	2.13
16854	Success	19/07/2019	2.13
16855	Success	19/07/2019	2.13
16890	Success	20/07/2019	2.13
16892	Success	20/07/2019	2.13
16900	Success	20/07/2019	2.13
16901	Success	20/07/2019	2.13
16942	Success	21/07/2019	2.13
16943	Success	21/07/2019	2.13
16945	Success	21/07/2019	2.13
16946	Success	21/07/2019	2.13
17800	Failed	10/08/2019	3.0
17801	Failed	10/08/2019	3.0
17802	Failed	10/08/2019	3.0
17803	Success	10/08/2019	3.0
18056	Success	17/08/2019	3.0
18066	Success	17/08/2019	3.0
18107	Success	18/08/2019	3.0
18109	Success	18/08/2019	3.0
18205	Success	21/08/2019	3.0
18210	Success	21/08/2019	3.0
18211	Success	21/08/2019	3.0
18217	Success	21/08/2019	3.0
18252	Success	22/08/2019	3.0
18255	Success	22/08/2019	3.0
18256	Success	22/08/2019	3.0
18263	Success	22/08/2019	3.0
18264	Success	22/08/2019	3.0
18295	Success	23/08/2019	3.0
18298	Success	23/08/2019	3.0
18303	Success	23/08/2019	3.0
18408	Failed	25/08/2019	3.0
18409	Success	25/08/2019	3.0
18874	Success	05/09/2019	3.1
18943	Success	07/09/2019	3.1
18944	Success	07/09/2019	3.1
18952	Success	07/09/2019	3.1
19109	Success	12/09/2019	3.1
19110	Success	12/09/2019	3.1
19111	Success	12/09/2019	3.1
19113	Success	12/09/2019	3.1
19117	Success	12/09/2019	3.1
19118	Failed	12/09/2019	3.1
19119	Success	12/09/2019	3.1
19153	Success	13/09/2019	3.1
19184	Success	14/09/2019	3.1
19185	Success	14/09/2019	3.1
19187	Success	14/09/2019	3.1
19189	Success	14/09/2019	3.1
19193	Success	14/09/2019	3.1
19194	Success	14/09/2019	3.1
19225	Failed	15/09/2019	3.1
19226	Failed	15/09/2019	3.1
19228	Success	15/09/2019	3.1

Like you'd get out of Octopus deploy.

[_Andrew_]

Thanks @fhill - that looks like an interesting approach, but I couldn't quite get it to work.  I'll keep a note of it though.

@v-yulgu-msft - that does exactly what I want thank you very much for your help.  I only needed to tweak earliest date last version slightly to work with my actual data.

I think a good think for me to investigate next would be having the measure react to any filters that are applied instead of hardcoding it in the measure.

Thank you.

[v-yulgu-msft · Accepted solution]

Hi @_Andrew_,

Suppose the data type of [Version] is numeric.

You need a [Rank] column in source table (suppose it's Table1).

Rank = RANKX(Table1,Table1[Version],,ASC,Dense)

Then, please create some measures as below:

earliest date this version = MIN(Table1[Completed Date]) 
earliest date last version =
CALCULATE (
    MIN ( Table1[Completed Date] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Rank]
            = MAX ( Table1[Rank] ) - 1
            && Table1[TaskState] = "Success"
    )
)

Interval =
SWITCH (
    TRUE (),
    [earliest date last version] < [earliest date this version], DATEDIFF ( [earliest date last version], [earliest date this version], DAY ),
    [earliest date last version] > [earliest date this version], -1
        * DATEDIFF ( [earliest date this version], [earliest date last version], DAY ),
    0
)

Add corresponding columns into Matrix.

Best regards,
Yuliana Gu

[fhill]

See if this gives you what you need...   you had your blank on the first line, but my logic moves the 'unkown' to the last line.  To do calcuations, I had to choose some 'date' to end the logic so I put code in there to always use MAX(Date) at the end of the file.  This is neccessary to 'skip' the final Version comparison b/c there's no higher versions to compare it too...  I also want to give a Shout Out to this AWESOME article that helped me understand EARLIER/EARLIST to write this 'first time use' code...  http://tinylizard.com/dax-earlier-function/

If anyone has an easier way, please feel free to jump in!

FOrrest

Calculated Clumns:

End of Version = IF( Table1[Version] = MAX(Table1[Version]),MAX(Table1[Date]), // Figure out what to do when we reach the MAX Version number since there's nothing higher than it to use to calcute a higher date...  I've put in the MAX date, but you will need to descide how you want to handel this???
CALCULATE( MIN (Table1[Date]), // For Non Max Version Numbers Get MIN Date by...
FILTER(ALL(Table1), Table1[Date] > EARLIER(Table1[Date])), // A Row GREATER Than the current Row for Date
FILTER(Table1, Table1[Version] > EARLIER(Table1[Version])), // A Row Greater Than the current Row for Version
FILTER(Table1, Table1[TaskState] = "Success") // That's a SUCCESS
) -1 ) // Go back 1 day & Close the IF Statement

Measures:

Min_Date = MIN (Table1[Date])

Interval = DATEDIFF([Min_Date],AVERAGE(Table1[End of Version]),DAY)

[Greg_Deckler]

Look into EARLIER and EARLIEST functions.