Rolling sum of a measure without month

[hlalibe]

Hi guys,

i've seen many examples for a rolling sum when using months or weeks or days. But my data does not contain any of these. I simply have a column with numbers and I want to keep a running sum of, say the last 3 rows for that measure. Example:

Index         Measure   Runing sum

1                1               1

2                3               4

3                2               6

4                4               9

I've tried that code in a new column called Rolling Sum:

Running Sum = CALCULATE(

SUM ( Table[Measure] ),

FILTER (

ALL ( Table ),

Table[Index] <= ( Table[Index] )

&& Table[Index]

)

)

>= ( Table[Index] ) - 3

But I only get the same value oever and over for this new column. It does not sum the last 3 rows. I'm a bit lost really.

[RandyPgh · Accepted solution]

Try this as a Measure.

Sum Last 3 Indices =

    IF( HASONEVALUE('Table'[Index]),

        VAR MyIndex = VALUES('Table'[Index])

        RETURN

        CALCULATE(

            SUM( 'Table'[RandomValue] ),

            FILTER(

                ALL('Table'),

                'Table'[Index] <= MyIndex && 'Table'[Index] >= MyIndex - 2

            )

        )

    )

[VasTg · Accepted solution]

@hlalibe  Try this...

Measure = VAR AA = MAX('Table'[Column1])-3

VAR AB = MAX('Table'[Column1])

RETURN CALCULATE(SUM('Table'[Column2]),'Table'[Column1]>AA&&'Table'[Column1]<=AB,ALL('Table'))

Mark it as answer it if satisfies the question.

[hlalibe]

@VasTg 

Thanks for your input.

I tried your code on 100 rows and it returns empty cells for each row. Not sure why. I checked HASONEVALUE and it seems to always return FALSE, could explain why i get empty cells ?

I also tried to run it on my entire table (490'000 rows x 17 columns) and it says "working on it..." but never finishes after 30 min

[hlalibe]

@RandyPgh 

thanks for your input,

If I keep the 2 MAX words I get always the same value for each row, which is the sum of the last 3 rows of my measure column.

If I remove the 2 MAX words then it works as I want. But it seems quite slow. I managed to test it on 100 rows, but I never got to the end of the calculation when running it on my full table (490'000 rows by 17 columns), it says "working on it..." even after 1 hour.

edit: I actually used your code for a new column. If I use it in a measure, then it works fine and requires the 2 MAX words. I still need to check if it is fast enough for my 490'000 rows

[RandyPgh]

Mine did not use MAX. I think you have the 2 responses mixed. No matter.

My DAX query was meant to be a measure. If you tried it as a Calculated Column, it probably was slow due to the context switching. Take some time to learn about the DAX Filter and Row Contexts. When you have a column calculation, you are running in a row context, but if your formula uses CALCULATE, it transitions the row context into a filter context. This is an expensive operation and thus adds some time to your expression.

When adding columns to your source data, it is best to consider your source queries. For example, if you are sourcing SQL Server and have control of the query that pulls the data, you can add some joins to get your results. Below is a crude example of a query. You maybe could use something fancier like LAG or a CTE with recursion, but this gives you a quick and understandable solution.

SELECT t1.Index, t2.Value,

             t1.Value + COALESCE(t2.Value, 0) + COALESCE(t3.Value, 0) AS SumOfLast3

  FROM dbo.MyTable AS t1

  LEFT OUTER JOIN dbo.MyTable AS t2 ON t1.Index = t2.Index + 1

  LEFT OUTER JOIN dbo.MyTable AS t3 ON t1.Index = t3.Index + 2;

[hlalibe]

@RandyPgh 

sorry, I inverted your user names above.

Indeed your code works if I use it in a measure and on 100 rows.

Now i've got to let it run on a larger number of rows to see how fast it goes.

will revert.

[RandyPgh]

Sorry, slight fix to query.

SELECT t1.Index, t1.Value,

             t1.Value + COALESCE(t2.Value, 0) + COALESCE(t3.Value, 0) AS SumOfLast3

  FROM dbo.MyTable AS t1

  LEFT OUTER JOIN dbo.MyTable AS t2 ON t1.Index = t2.Index + 1

  LEFT OUTER JOIN dbo.MyTable AS t3 ON t1.Index = t3.Index + 2;

[RandyPgh · Accepted solution]

Try this as a Measure.

Sum Last 3 Indices =

    IF( HASONEVALUE('Table'[Index]),

        VAR MyIndex = VALUES('Table'[Index])

        RETURN

        CALCULATE(

            SUM( 'Table'[RandomValue] ),

            FILTER(

                ALL('Table'),

                'Table'[Index] <= MyIndex && 'Table'[Index] >= MyIndex - 2

            )

        )

    )