using Virtual tables to get running total

Hi BIuser09,

Thank you for reaching out to the Microsoft Fabric Forum Community.

The issue arises because DAX measures must return a single scalar value, while your virtual table correctly computes row-wise running totals that can’t be directly surfaced in a measure. Even though your virtual table logic is valid and produces accurate results, DAX cannot preserve row context across visual columns in a measure. As a result, expressions like MAXX or SUMX over the virtual table collapse the context and only return individual deltas instead of a cumulative result.

If this solution helped, please consider marking the response as accepted and giving it a thumbs-up so others can benefit as well.

Best regards,
Prasanna Kumar

Hi,

This week I found the solution, thanks to a colleague.
It is possible and I would like to share this with you.

Issue was the last line. You'll need to filter to 1 record in the virtual table. Use calendar dimension used in the matrix.

Note also that all filter fields (in FACT and Dimension tables) will have to be defined in the Calculate of VAR T1

TableRunningTotal = 
VAR curM = VALUE(CONCATENATE(YEAR(now()), FORMAT(MONTH(now()),"00")) )
VAR prevM = IF(MONTH(today())=1,
    VALUE(CONCATENATE(YEAR(today())-1, "12")),
    VALUE(CONCATENATE(YEAR(today()), FORMAT(MONTH(TODAY())-1,"00")))
)

VAR T1 = SUMMARIZE(
        FILTER(ALL(DATA),VALUE([Month]) >= prevM), 
        'Calendar'[YearMonth],
        "measure1", CALCULATE(sum(DATA[Value]), DATA[Measure] = "1"),
        "measure_X", CALCULATE(sum(DATA[Value]), DATA[Measure] = "X"),
        "measure_Y", CALCULATE(sum(DATA[Value]), DATA[Measure] = "Y"),
    )
VAR T2 = ADDCOLUMNS(T1, "measure2", if(VALUE([YearMonth]) < curM, [measure_X], [measure_Y]) )
VAR T3 = ADDCOLUMNS(T2, "Delta", [measure1]-[ measure2] )
VAR T4 = ADDCOLUMNS(T3,"RunningTotal", SUMX(FILTER(T3, [YearMonth]<=EARLIER([YearMonth])), [Delta]) )
RETURN
     MAXX(FILTER(T4,[YearMonth] = SELECTEDVALUE(Calendar[YearMonth])), [Cum_Delta])