Forum Discussion
Moving Range with time
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.
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
2 Replies
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
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
