Difference between two rows

Unknown — Tue, 07 Jun 2022 09:20:53 GMT

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

Re: Difference between two rows

tamerj1 — Tue, 07 Jun 2022 10:43:16 GMT

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 ) )

topic Difference between two rows in DAX Commands and Tips

Difference between two rows

Re: Difference between two rows