Cumulative Running Total on virtual Table

[Anonymous]

Hi Team,

I have created a virtual table using DAX, I need to calculate a new column with the cumulative total using the virtual table.

Kindly suggest possibilities.

Thanks in Advance,

[pellow17]

@mahoneypat Although you posted this three years ago, many thanks! I had been searching for this answer for hours!

[mahoneypat · Accepted solution]

It may be better to do this with a measure instead of generated a running total in a variable, but here is an example that shows how to approach this.  The first variable is just a table with Index and Value, and the 2nd variable shows how to add the RT column (you'll need a column that has a date, index, etc. to use for filtering).  This is a table expression but you could return a scalar variable to return a calculation with the added column as a measure.

DemoTable =
VAR vTable =
    DATATABLE (
        "Index", INTEGER,
        "Value", INTEGER,
        {
            { 1, 2 },
            { 2, 4 },
            { 3, 6 }
        }
    )
VAR vAddRT =
    ADDCOLUMNS (
        vTable,
        "RT",
            VAR thisrowindex = [Index]
            RETURN
                SUMX ( FILTER ( vTable, [Index] <= thisrowindex ), [Value] )
    )
RETURN
    vAddRT

Pat