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

fhill's avatar
fhill
Icon for Resident Rockstar rankResident Rockstar
9 years ago

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)