Cumulative Column in a Query Model with Virtual Tables

[CupidC]

Hello everyone,

I'm seeking advice on how to generate a cumulative column within the query model, - dealing with a virtual rather than a physical table.

For instance, I encounter an error when attempting to use the following formula. I've marked my desired outcome in red in the attached example, but I'm uncertain about the correct approach to achieve this result. Any guidance would be greatly appreciated.

[amitchandak · Accepted solution]

@CupidC ,

Try a new column

Sumx(filter(Table, [Value] <= Earlier([Value]) ), [Value])

[CupidC · Accepted solution]

Hi Amitchandak,

Thank you! It's working perfectly. I have another question: is there a possibility to use a variable instead of using  Earlier? I've come across several posts recommending against this function and was wondering if there's an alternative method involving variables or something else..  thanks!

Thanks

Cupid

[v-heq-msft]

Hi @CupidC ,
Here is a dax expression that does not use the earlier function:

Accumulation = 
CALCULATE(
    SUM('Table'[VALUE]),
    FILTER(
        ALLEXCEPT('Table','Table'[ID]),
        'Table'[VALUE] <= MAX('Table'[VALUE])
        )
    )

Final output

For more information about the operation of accumulation you can refer to this blog
How to Calculate Cumulative Values with DAX - Microsoft Fabric Community

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[CupidC]

Hi Albet

Thanks,, but the table is created virtually, so i can't reference "Table" in query mode, do you have other ways to achieve the result..?

[v-heq-msft · Accepted solution]

Hi @CupidC ,
You can try this

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

Final output

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak · Accepted solution]

@CupidC ,

Try a new column

Sumx(filter(Table, [Value] <= Earlier([Value]) ), [Value])

[CupidC · Accepted solution]

Hi Amitchandak,

Thank you! It's working perfectly. I have another question: is there a possibility to use a variable instead of using  Earlier? I've come across several posts recommending against this function and was wondering if there's an alternative method involving variables or something else..  thanks!

Thanks

Cupid