Forum Discussion

BIuser09

Helper I

1 year ago

Solved

using Virtual tables to get running total

Hi,

Would like to use virtual table to have a running sum on measure delta like matrix below.

Delta works fine. is based on measure 1 and 2.

Measure 2 is special: data depends on calendar!YearMonth:

data previous month (202504) is something else compared to current month and later

YearMonth are also columns in matrix.

Should be
	202504	202505	202506	202507	202508	202509	202510	202511	202512
measure1	100	100	100	100	100	100	100	100	100
measure2	50	60	60	60	60	60	60	60	60
measure delta	50	40	40	40	40	40	40	40	40
running total	50	90	130	170	210	250	290	330	370

But is
	202504	202505	202506	202507	202508	202509	202510	202511	202512
measure1	100	100	100	100	100	100	100	100	100
measure2	50	60	60	60	60	60	60	60	60
measure delta	50	40	40	40	40	40	40	40	40
running total	50	150	250	350	450	550	650	750	850

Code below shows me correct running total but I can not use it as a measure.

It always shows me delta instead of running total.

How can I use 'running total' in my measure ?

And also, if any selections in PROD and GEO dimension tables, will these be reflected in this measure, using virtual tables ? Or should I capture selection in the measure ?

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
     --T4 : shows in table view (create table) ==> correct running total
     --SUMX(T4,[RunningTotal]) : shows only delta’s per YearMonth
     --MAXX(T4,[RunningTotal]) : shows only delta’s per YearMonth
     --SELECTCOLUMNS(T4," ", [RunningTotal]) : shows only delta’s per YearMonth

DAX

Virtual Table

Anonymous
1 year ago
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

BIuser09

1 year ago

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])

13 Replies

Anonymous
Not applicable
1 year ago
Hi BIuser09

Thanks for reaching out to the Microsoft Fabric Forum Community.

Try Using fallowing Dax.

Running Total Delta =
VAR CurrentYearMonth = SELECTEDVALUE('Calendar'[YearMonth])
VAR AllYearMonths =
FILTER(
ALLSELECTED('Calendar'[YearMonth]),
'Calendar'[YearMonth] <= CurrentYearMonth
)

VAR RunningSum =
SUMX(
AllYearMonths,
VAR YM = 'Calendar'[YearMonth]
VAR Measure1 =
CALCULATE(
SUM(DATA[Value]),
DATA[Measure] = "1",
'Calendar'[YearMonth] = YM
)
VAR Measure2 =
CALCULATE(
IF(
YM < VALUE(FORMAT(TODAY(), "YYYYMM")),
SUM(DATA[Value]),
BLANK()
),
DATA[Measure] = "X"
)
+
CALCULATE(
IF(
YM >= VALUE(FORMAT(TODAY(), "YYYYMM")),
SUM(DATA[Value]),
BLANK()
),
DATA[Measure] = "Y"
)
RETURN
Measure1 - Measure2
)

RETURN
RunningSum

If this helped, please mark the response as the accepted solution and give it a thumbs-up so others can benefit too.

Best regards,
Prasanna Kumar
- BIuser09
  Helper I
  1 year ago
  Thanks, this works.
  However, it is not using virtual table I pasted above.
  was trying to understand the virtual table and using it in a measure
bhanu_gautam
Super User
1 year ago
BIuser09 ,Create a measure for the delta calculation.

Measure Delta =
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])
RETURN
SUMX(T3, [Delta])

Now create one for running total

Running Total =
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(T4, [RunningTotal])
BIuser09
Helper I
1 year ago
Thanks for the effort, but you split the code into 2 separate measures and you don't reference the 1st one in the second measure.
This does not work.
Or am I misunderstanding your answer?

Furthermore, the selection in dimension tables are not reflected in the result.
Anonymous
Not applicable
1 year ago
Hi BIuser09,

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

Try this dax measure with virtual table

Running Total Delta =
VAR CurrentYM = SELECTEDVALUE('Calendar'[YearMonth])

VAR curM = VALUE(FORMAT(TODAY(), "YYYYMM"))
VAR prevM = VALUE(FORMAT(EDATE(TODAY(), -1), "YYYYMM"))

VAR T1 =
SUMMARIZE(
FILTER(
ALL('Calendar'[YearMonth], DATA),
'Calendar'[YearMonth] >= 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(
[YearMonth] < curM,
[measure_X],
[measure_Y]
)
)

VAR T3 =
ADDCOLUMNS(
T2,
"Delta", [measure1] - [measure2]
)

VAR T4 =
ADDCOLUMNS(
T3,
"RunningTotal",
VAR YM = [YearMonth]
RETURN
SUMX(
FILTER(T3, [YearMonth] <= YM),
[Delta]
)
)

RETURN
MAXX(
FILTER(T4, [YearMonth] = CurrentYM),
[RunningTotal]
)

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
- BIuser09
  Helper I
  1 year ago
  it also returns deltas instead of running total
Anonymous
Not applicable
1 year ago
Hi BIuser09,

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

Try this dax measure with virtual table

Running Total Delta =
VAR CurrentYM = SELECTEDVALUE('Calendar'[YearMonth])

VAR curM = VALUE(FORMAT(TODAY(), "YYYYMM"))
VAR prevM = VALUE(FORMAT(EDATE(TODAY(), -1), "YYYYMM"))

VAR T1 =
SUMMARIZE(
FILTER(
ALL('Calendar'[YearMonth], DATA),
'Calendar'[YearMonth] >= 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,
"Delta",
VAR YM = [YearMonth]
VAR measure2 =
IF(
YM < curM,
[measure_X],
[measure_Y]
)
RETURN
[measure1] - measure2
)

VAR RunningTotal =
SUMX(
FILTER(
T2,
[YearMonth] <= CurrentYM
),
[Delta]
)

RETURN
RunningTotal

If the issue persists, please share a sample PBIX file with representative data. This will help in accurately replicating and resolving the problem.

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
Anonymous
Not applicable
1 year ago
Hi BIuser09,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful this will benefit others in the community as well.

Best regards,

Prasanna Kumar
- BIuser09
  Helper I
  1 year ago
  Hi,
  thanks for the responses but I did not get an answer on my question.
  I do have a solution for my running total in a single measure.
  But i wanted to make a measure on my virtual table as I originally requested (see above).
  This virtual table is completely logical and easy to build. Correct answer is in the virtual table but cannot be used in a measure. which is a disappointment.
Anonymous
Not applicable
1 year ago
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
- BIuser09
  Helper I
  1 year ago
  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])
Anonymous
Not applicable
1 year ago
Hi BIuser09,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful this will benefit others in the community as well.

Best regards,

Prasanna Kumar
Anonymous
Not applicable
1 year ago
Hi BIuser09,

Just a gentle reminder has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

Forum Discussion

using Virtual tables to get running total

13 Replies

Related content

Fabric virtual table

Dax Logic Issue virtual Table

Dataverse Virtual table link to Fabric

Cumulative Running Total on virtual Table

GA availability for streaming connector virtual network