Simple Rolling Sum not summing correctly

Hi All,

I have been struggling to get my Rolling Sum Running despite doing it multiple different ways. It seems that its not picking up all the dates that are within the period that i have asked for.

For context, I have 60min rainfall data that is being live streamed from a rain gauge in direct query, the data is then pulled into multiple different graphs like hourly rainfall (Raw) and Daily averages (bins)  and lastly rolling averages(which is failing).

The data is simply a datetime column 'Rain gauge[NZDT]' and a rainfall column 'Rain gauge'[Hourly Rainfall]'

Here is my Measure (i cannot use calulated columns because of direct query) :

 

24 Hour Rolling =

CALCULATE(

    SUM('Rain gauge'[Hourly Rainfall]),

    DATESINPERIOD('Rain gauge'[NZDT],LASTDATE('Rain gauge'[NZDT]),1,DAY))

 

This produces a graph that says the rolling rainfall is 4.8mm, 

but the rainfall in the last day has been more than 11mm. so it doesnt seem to be taking in all the timestamps

 

 

As i said, i have also tried: 

Running_Total =

CALCULATE (

SUM ( 'Rain gauge'[Hourly Rainfall]),

FILTER (

ALL('Rain gauge'),

'Rain gauge'[NZDT] <= SELECTEDVALUE('Rain gauge'[NZDT])),

FILTER (

ALL('Rain gauge'),

'Rain gauge'[NZDT] >= SELECTEDVALUE('Rain gauge'[NZDT]) - 1))

 

and for 7 day:

 

7day Cumulative =

var sevendaysago = MAX('Rain gauge'[NZDT])-7

var today = Max('Rain gauge'[NZDT])

return

CALCULATE(SUM('Rain gauge'[Hourly Rainfall]),('Rain gauge'[NZDT] >= sevendaysago),'Rain gauge'[NZDT] < today)

All do not seem to grab the data,

Any help would be much appreciated 🙂