Forum Discussion

Unknown's avatar
Unknown
Frequent Visitor
4 years ago
Solved

Difference between two rows

Hello,

I have a table that looks like this (Please note that my date is in the format DD/MM/YYYY):


I want to calculate the difference between two following dates where condition=1.
I managed to create the column in orange with the following DAX code:
datediff = IF( Table[condition]=1, Table[date] - CALCULATE( SUM(Table[date]), FILTER( Table[index] = EARLIER(Table[index])-1 ) ), BLANK() )
But I want to get the values in the yellow column. Can you help me achieve this without having to create a new index column?
Thank you

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Unknown 
    You can use

     

     

    datediff =
VAR CurrentDate = 'Table'[date]
VAR PreviousTable =
    FILTER (
        'Table',
        'Table'[date] < CurrentDate
            && 'Table'[condition] <> BLANK ()
    )
VAR PreviousDate =
    MAXX ( PreviousTable, 'Table'[date] )
RETURN
    IF (
        'Table'[condition] = 1
            && NOT ISEMPTY ( PreviousTable ),
        DATEDIFF ( PreviousDate, CurrentDate, DAY )
    )

     

     

1 Reply

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Unknown 
    You can use

     

     

    datediff =
VAR CurrentDate = 'Table'[date]
VAR PreviousTable =
    FILTER (
        'Table',
        'Table'[date] < CurrentDate
            && 'Table'[condition] <> BLANK ()
    )
VAR PreviousDate =
    MAXX ( PreviousTable, 'Table'[date] )
RETURN
    IF (
        'Table'[condition] = 1
            && NOT ISEMPTY ( PreviousTable ),
        DATEDIFF ( PreviousDate, CurrentDate, DAY )
    )