cancel
Showing results for
Did you mean:
Frequent Visitor

## Running total last 5 seconds data

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

1 ACCEPTED SOLUTION
Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

3 REPLIES 3
Super User IV

You should be able to create a column like:

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

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

Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements

#### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors