Moving Range with time

[Anonymous]

I'm trying to get the time from one row to the next to determine how much time has elapsed from each entry.

I'm currently using this DAX statement: 

Moving Range = 
VAR a =
    CALCULATE (
        FIRSTNONBLANK ( Query1[Time], 1 ),
       FILTER(
        'Query1',
        'Query1'[Test]= EARLIER('Query1'[Test] - 1  ))
    )
RETURN
    IF ( a = BLANK (), BLANK(), DATEDIFF( a , Query1[Time],MINUTE))

But it gives me an error when I use it. I've posted some sample data to assist with this.

Same Day: is an index that indicates to me that the entry is the same day.

Test:  is the index of the events that are on the same day.

 

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous, 

You can add an index column in Query Editor. Then create a calculated column like below: 

Moving Range = var pre= CALCULATE(SUM('Table'[Time]),FILTER('Table','Table'[Same day Count]=EARLIER('Table'[Same day Count])&&'Table'[Index]=EARLIER('Table'[Index])-1))

return IF(pre=BLANK(),BLANK(),DATEDIFF(pre,'Table'[Time],MINUTE))

Best Regards,
Qiuyun Yu

[HotChilli]

First question : what's the error?

Doesn't the offending text get highlighted in the formula?

It looks like there's a bracket out of place in the EARLIER clause