Forum Discussion

MatteMatto's avatar
MatteMatto
Frequent Visitor
6 years ago

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 ...
MatteMatto's avatar
MatteMatto
Frequent Visitor
6 years ago
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))
 
 

 

 

 

InstallationNZDTRain
Rain Gauge2020-06-18 11:04:15.000 +12:002
Rain Gauge2020-06-18 10:04:15.000 +12:000.4
Rain Gauge2020-06-18 09:04:16.000 +12:000.4
Rain Gauge2020-06-18 08:04:15.000 +12:001.4
Rain Gauge2020-06-18 07:04:15.000 +12:000.2
Rain Gauge2020-06-18 06:04:15.000 +12:001.8
Rain Gauge2020-06-18 05:04:14.000 +12:001.8
Rain Gauge2020-06-18 04:04:15.000 +12:001.2
Rain Gauge2020-06-18 03:04:15.000 +12:001
Rain Gauge2020-06-18 02:04:15.000 +12:000.6
Rain Gauge2020-06-18 01:04:15.000 +12:000.2
Rain Gauge2020-06-18 00:04:14.000 +12:000
Rain Gauge2020-06-17 23:04:15.000 +12:000
Rain Gauge2020-06-17 22:04:14.000 +12:000
Rain Gauge2020-06-17 21:04:14.000 +12:000
Rain Gauge2020-06-17 20:04:14.000 +12:000
Rain Gauge2020-06-17 19:04:15.000 +12:000.2
Rain Gauge2020-06-17 18:04:15.000 +12:000
Rain Gauge2020-06-17 17:04:18.000 +12:000
Rain Gauge2020-06-17 16:04:14.000 +12:000
Rain Gauge2020-06-17 15:04:14.000 +12:000
Rain Gauge2020-06-17 14:04:15.000 +12:000
Rain Gauge2020-06-17 13:04:14.000 +12:000
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

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

 

  • MatteMatto's avatar
    MatteMatto
    Frequent Visitor
    6 years ago

    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's avatar
      lbendlin
      Icon for Super User rankSuper User
      6 years ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        6 years ago

        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.