SUM next rows for each row with DAX

[Mogry]

Hey guys,

I am wondering how I can make a SUM for each row including the values of the next nine rows? Like this:

The current row value (5) + the values of the next nine row values (48) put in the current row (53) and so on

 Value   Date   New Column or Measure

5                      53

5                      54

5                      55

5

5

5

5

5

6

7

8

9

To specify this one: I like to have the value based on the date. For each date I want the sum of the value of the date + the following nine days.

F.e. I have a measure like this:

Measure = CALCULATE(SUMX(Table,Table[VALUES]), FILTER(Table, Table[MODEL] = "Normal" && Table[DATE] <= Table[DATE]+9))

If I put this measue and the date in a table I just get the normal value for each day.

Should not be that complicated, but I can´t figure out how this will work with DAX in Power Bi. Thanks 🙂

[Zubair_Muhammad]

@Mogry

Hi,

Try this calculated column

=
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        'Table',
        'Table'[Model] = "Normal"
            && 'Table'[Date] >= EARLIER ( 'Table'[Date] )
            && 'Table'[Date]
                <= EARLIER ( 'Table'[Date] ) + 9
    )
)

[Mogry]

Thank you @Zubair_Muhammad. This calculated column works and give me the correct values.

Do you also have an idea for do this in a measure?

I can "translate" it into a measure like this:

Measure = 
CALCULATE (
   SUM(Table[Value]),
    FILTER(ALL(Table),
     COUNTROWS( 
      FILTER (
        Table,
         Table[Model] = "Normal"
            && EARLIER(Table[Date]) >= Table[Date] 
            && EARLIER(Table[Date]) <= Table[Date] + 9 
              )
           )
    )
)

But my values got summarized in a strange way.

My thoughts are, that a measure would be more nice, because I have three different models.

[Zubair_Muhammad · Accepted solution]

Hi @Mogry

As a MEASURE, use this

Measure =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Model] = "Normal"
            && 'Table'[Date] >= SELECTEDVALUE ( 'Table'[Date] )
            && 'Table'[Date]
                <= SELECTEDVALUE ( 'Table'[Date] ) + 9
    )
)