Forum Discussion

_Andrew_'s avatar
_Andrew_
Regular Visitor
9 years ago
Solved

Difference between two dates in two rows in a matrix

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 t...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

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