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

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'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?