Rolling sum of some seconds values

[Anonymous]

HI,

I am quite new to Power BI. I have a table with2 columns that are times in minutes  as below and a measure "Values" of my items in that specific second. I tried to calculate the rolling sum of the last 5 seconds with this measure 

# Meas =

CALCULATE (

SUM('TT data'[Value]),

FILTER ('TT data',

'TT data'[Time_of_the_Measure] >= MAX ( 'TT data'[Time_of_the_Measure] )

&& 'TT data'[Time_of_the_Measure] <= MAX('TT data'[Time minus 5sec]

)

))

but it does not work.

How to do that?

Time_of_the_Measure	Time minus 5sec	# Theta	# Meas
1899-12-30 12:00:00	1899-12-30 11:59:55	811
1899-12-30 12:00:01	1899-12-30 11:59:56	1509
1899-12-30 12:00:02	1899-12-30 11:59:57	1290
1899-12-30 12:00:03	1899-12-30 11:59:58	1295
1899-12-30 12:00:04	1899-12-30 11:59:59	2409
1899-12-30 12:00:05	1899-12-30 12:00:00	4122
1899-12-30 12:00:06	1899-12-30 12:00:01	4266
1899-12-30 12:00:07	1899-12-30 12:00:02	4637
1899-12-30 12:00:08	1899-12-30 12:00:03	4314
1899-12-30 12:00:09	1899-12-30 12:00:04	4365
1899-12-30 12:00:10	1899-12-30 12:00:05	4131
1899-12-30 12:00:11	1899-12-30 12:00:06	3848
1899-12-30 12:00:12	1899-12-30 12:00:07	3590
1899-12-30 12:00:13	1899-12-30 12:00:08	3527
1899-12-30 12:00:14	1899-12-30 12:00:09	3834
1899-12-30 12:00:15	1899-12-30 12:00:10	3683
1899-12-30 12:00:16	1899-12-30 12:00:11	3639
1899-12-30 12:00:17	1899-12-30 12:00:12	3906

[jdbuchanan71]

Hello @Anonymous ,

I think you just had your compare signs flipped so so lines matched.

# Meas =
CALCULATE (
    SUM ( 'TT data'[Value] ),
    FILTER (
        'TT data',
        'TT data'[Time_of_the_Measure] <= MAX ( 'TT data'[Time_of_the_Measure] )
            && 'TT data'[Time_of_the_Measure] >= MAX ( 'TT data'[Time minus 5sec] )
    )
)

[Anonymous]

hi, it seems it just copies the line

Time_of_the_Measure	Time minus 5sec	# Theta	# Meas
1899-12-30 12:00:00	1899-12-30 11:59:55	811	811
1899-12-30 12:00:01	1899-12-30 11:59:56	1509	1509
1899-12-30 12:00:02	1899-12-30 11:59:57	1290	1290
1899-12-30 12:00:03	1899-12-30 11:59:58	1295	1295
1899-12-30 12:00:04	1899-12-30 11:59:59	2409	2409
1899-12-30 12:00:05	1899-12-30 12:00:00	4122	4122
1899-12-30 12:00:06	1899-12-30 12:00:01	4266	4266
1899-12-30 12:00:07	1899-12-30 12:00:02	4637	4637
1899-12-30 12:00:08	1899-12-30 12:00:03	4314	4314
1899-12-30 12:00:09	1899-12-30 12:00:04	4365	4365
1899-12-30 12:00:10	1899-12-30 12:00:05	4131	4131
1899-12-30 12:00:11	1899-12-30 12:00:06	3848	3848
1899-12-30 12:00:12	1899-12-30 12:00:07	3590	3590
1899-12-30 12:00:13	1899-12-30 12:00:08	3527	3527
1899-12-30 12:00:14	1899-12-30 12:00:09	3834	3834
1899-12-30 12:00:15	1899-12-30 12:00:10	3683	3683
1899-12-30 12:00:16	1899-12-30 12:00:11	3639	3639
1899-12-30 12:00:17	1899-12-30 12:00:12	3906	3906
1899-12-30 12:00:18	1899-12-30 12:00:13	3809	3809

I'm using this measure as you suggested. columns are time data type.

# Meas =

CALCULATE (

SUM('TT data'[B: Theta mean (uV) 4-8 Hz]),

FILTER ('TT data',

'TT data'[Time_of_the_Measure] <= MAX ( 'TT data'[Time_of_the_Measure] )

&& 'TT data'[Time_of_the_Measure] >= MAX('TT data'[Time minus 5sec]

)

))

BR

Roberto

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

Use this one.

Measure test = 
CALCULATE (
    SUM ( 'TT data'[# Meas] ),
    FILTER (
        ALL('TT data'[Time_of_the_Measure],'TT data'[Time minus 5sec]),
        'TT data'[Time_of_the_Measure] <= MAX ( 'TT data'[Time_of_the_Measure] )
            && 'TT data'[Time_of_the_Measure] >= MAX ( 'TT data'[Time minus 5sec] )
    )
)