find datediff between date and direct next date with user filter

[eliasayyy]

hello everyone, i have a table, 
"transaction table"

i made a table visual that has transaction date and nb  of transactions made

as a filter slicer, i have userID

here is an example

i need to add a new measure that will calculate the dattdif between each row of date

so for first row 0 becasue there is no previous date

then from first row to second row

there is 30 days almost so 30 days

then between second row and third row there is a diff of 1 day

..

after that i need to find the average so i can know how much this customer returns on average

So if we only take the first 2 calcultaions

30 + 1 = 31/2 = 15 days average

[Dangar332 · Accepted solution]

hi, @eliasayyy 

try below

Measure 16 = 
var a  = ADDCOLUMNS(
    ALL('transaction table'[transaction date],'transaction table'[transaction],'transaction table'[userid]),
    "datediff",
    var a = 'transaction table'[transaction date]
    var b =
         MINX(
           FILTER(
              'transaction table',
              'transaction table'[userid]=EARLIER('transaction table'[userid]) &&
              'transaction table'[transaction date]>a
            ),
              'transaction table'[transaction date]
        )
    var c= IF(b=BLANK(),a,b)
    RETURN
    DATEDIFF('transaction table'[transaction date],c,DAY)
)
RETURN
AVERAGEX(FILTER(a,'transaction table'[transaction date]<=MAX('transaction table'[transaction date])),[datediff])