Running total last 5 seconds data

[rluongo]

Hi All,

my table is very simple 

Time	Time_5secBack	Value	RunningTotal
02:16:41	02:16:36	6.595522
02:16:42	02:16:37	8.453462
02:16:43	02:16:38	6.175027
02:16:44	02:16:39	7.015015
02:16:45	02:16:40	3.09251	31.33154
02:16:46	02:16:41	3.381336	28.11735
02:16:47	02:16:42	3.423026	23.08691
...	...	...

I have to calculate the cumulative value of the last 5 seconds so basically

from sec 41 to 44 - 0

sec 45  - the sum of the previous 5 seconds - 31.33

sec 46 - the sum of the previous 5 seconds - 28.11

It is very easy in excel this sliding sum but I do not know how to replicate in in dax.

Any help appreciated

Roberto

[Greg_Deckler]

You should be able to create a column like:

Running Total =
  SUMX(FILTER('Table',[Time] >= [Time_5secBack] && [Time] <= EARLIER([Time])),[Value])

[rluongo]

Thanks but I would like to work with a measure and EARLIER is not suggested

[Greg_Deckler]

Just use a var, this should be the equivalent measure:

Running Total =
  VAR __Time = MAX('Table'[Time]) //max of time column in current context (measure)
  VAR __Time5s = MAX('Table'[Time_5secBack])
RETURN
  SUMX(FILTER(ALL('Table'),[Time] >= __Time5s && [Time] <= __Time),[Value])