Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
You should be able to create a column like:
Running Total =
SUMX(FILTER('Table',[Time] >= [Time_5secBack] && [Time] <= EARLIER([Time])),[Value])
Thanks but I would like to work with a measure and EARLIER is not suggested
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])
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |