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
Super 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

    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
     
    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
    Rain Gauge2020-06-17 12:04:14.000 +12:000
     
     
     
     
     
     
     
     
     
     
     
  • 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
      Super 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?