Simple Rolling Sum not summing correctly

[MatteMatto]

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 🙂 

[lbendlin]

I would put the SELECTEDVALUE('Rain gauge'[NZDT] as variable in front of the Calculate.

If you can post some sample data this should be a quick one.

[MatteMatto]

Amendments made to this one below, still no luck.

Running_Total =

var thispoint = SELECTEDVALUE('Rain gauge'[NZDT])

return

CALCULATE (

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

FILTER (

ALL('Rain gauge'),

'Rain gauge'[NZDT] <= thispoint),

FILTER (

ALL('Rain gauge'),

'Rain gauge'[NZDT] >= thispoint - 1))

Installation	NZDT	Rain
Rain Gauge	2020-06-18 11:04:15.000 +12:00	2
Rain Gauge	2020-06-18 10:04:15.000 +12:00	0.4
Rain Gauge	2020-06-18 09:04:16.000 +12:00	0.4
Rain Gauge	2020-06-18 08:04:15.000 +12:00	1.4
Rain Gauge	2020-06-18 07:04:15.000 +12:00	0.2
Rain Gauge	2020-06-18 06:04:15.000 +12:00	1.8
Rain Gauge	2020-06-18 05:04:14.000 +12:00	1.8
Rain Gauge	2020-06-18 04:04:15.000 +12:00	1.2
Rain Gauge	2020-06-18 03:04:15.000 +12:00	1
Rain Gauge	2020-06-18 02:04:15.000 +12:00	0.6
Rain Gauge	2020-06-18 01:04:15.000 +12:00	0.2
Rain Gauge	2020-06-18 00:04:14.000 +12:00	0
Rain Gauge	2020-06-17 23:04:15.000 +12:00	0
Rain Gauge	2020-06-17 22:04:14.000 +12:00	0
Rain Gauge	2020-06-17 21:04:14.000 +12:00	0
Rain Gauge	2020-06-17 20:04:14.000 +12:00	0
Rain Gauge	2020-06-17 19:04:15.000 +12:00	0.2
Rain Gauge	2020-06-17 18:04:15.000 +12:00	0
Rain Gauge	2020-06-17 17:04:18.000 +12:00	0
Rain Gauge	2020-06-17 16:04:14.000 +12:00	0
Rain Gauge	2020-06-17 15:04:14.000 +12:00	0
Rain Gauge	2020-06-17 14:04:15.000 +12:00	0
Rain Gauge	2020-06-17 13:04:14.000 +12:00	0

[lbendlin]

Just tried your Running Total query and it seems to work fine. How does it look on your side?

[MatteMatto]

Wow, that grinds my gears a bit i have just written it in word for word and look at what i get :

In that case, the only difference is that maybe the data is in direct query? could that effect this?

[lbendlin]

That is indeed bizarre.  Let me try direct query on my side, but I am pretty sure the reason is elsewhere.

Can you check that your "hourly rainfall" column is indeed set to "sum" ?  Or is it a measure that is influenced from elsewhere?

[lbendlin]

Tried with Direct Query, got the same result as I got with import. The only tricky part was to find the right column type for the NZDT column in my database, but I don't think it has an impact. We need to look elsewhere.

[MatteMatto]

Hi,

I ended up having chat with a Developer colleague and we worked through a lot of different options looking into why it was missing certain rows of data, He thinks that we have hit a brick wall in power bi itself as it was not properly pulling all the timestamps, In the end the answer was to move the cumulative calculation into the SQL Query. Sadly this still leaves power bi broken as far as im concerned.

[lbendlin]

Weird that it is only broken for you.  Unless your colleage has experienced the same issue on their PC.  Maybe it's a timestamp/timezone issue?

[MatteMatto]

Hi there,

I messe up and it turns out i cant use the datesbetween because of duplicates in the table, i suspect this is because my data comes as hourly and not daily. however this is the one below that also returns the same, i have altered it as you have said with no luck.

I have just tried this:

Running_Total =

var thispoint = SELECTEDVALUE('Rain gauge'[NZDT])

return

CALCULATE (

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

FILTER (

ALL('Rain gauge'),

'Rain gauge'[NZDT] <= thispoint),

FILTER (

ALL('Rain gauge'),

'Rain gauge'[NZDT] >= thispoint - 1))

The same answer is coming up, sample data below for the last day

Installation	NZDT	Rain
Rain Gauge	2020-06-18 11:04:15.000 +12:00	2
Rain Gauge	2020-06-18 10:04:15.000 +12:00	0.4
Rain Gauge	2020-06-18 09:04:16.000 +12:00	0.4
Rain Gauge	2020-06-18 08:04:15.000 +12:00	1.4
Rain Gauge	2020-06-18 07:04:15.000 +12:00	0.2
Rain Gauge	2020-06-18 06:04:15.000 +12:00	1.8
Rain Gauge	2020-06-18 05:04:14.000 +12:00	1.8
Rain Gauge	2020-06-18 04:04:15.000 +12:00	1.2
Rain Gauge	2020-06-18 03:04:15.000 +12:00	1
Rain Gauge	2020-06-18 02:04:15.000 +12:00	0.6
Rain Gauge	2020-06-18 01:04:15.000 +12:00	0.2
Rain Gauge	2020-06-18 00:04:14.000 +12:00	0
Rain Gauge	2020-06-17 23:04:15.000 +12:00	0
Rain Gauge	2020-06-17 22:04:14.000 +12:00	0
Rain Gauge	2020-06-17 21:04:14.000 +12:00	0
Rain Gauge	2020-06-17 20:04:14.000 +12:00	0
Rain Gauge	2020-06-17 19:04:15.000 +12:00	0.2
Rain Gauge	2020-06-17 18:04:15.000 +12:00	0
Rain Gauge	2020-06-17 17:04:18.000 +12:00	0
Rain Gauge	2020-06-17 16:04:14.000 +12:00	0
Rain Gauge	2020-06-17 15:04:14.000 +12:00	0
Rain Gauge	2020-06-17 14:04:15.000 +12:00	0
Rain Gauge	2020-06-17 13:04:14.000 +12:00	0
Rain Gauge	2020-06-17 12:04:14.000 +12:00	0