topic Re: running total goes wrong in DAX Commands and Tips

running total goes wrong

BIuser09 — Thu, 15 May 2025 12:11:57 GMT

Struggling for some time now in this issue.

Would like 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.

Running total measure goes wrong. It adds up measure1 instead of measure delta.

Any suggestions ?

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

measure2 =	if( SELECTEDVALUE('Calendar'[YearMonth]) >= VALUE(CONCATENATE(YEAR(now()), FORMAT(MONTH(now()),"00")) ),
	[measure_X]
	,
	[measure_Y]
	)

measure delta =	measure1 - measure2

running total =	CALCULATE(
	[measure delta],
	FILTER(
	ALLSELECTED('Calendar'[YearMonth]),
	'Calendar'[YearMonth] <= MAX('Calendar'[YearMonth])
	)
	)

Re: running total goes wrong

v-pnaroju-msft — Fri, 16 May 2025 04:51:32 GMT

Hi BIuser09,

We appreciate your inquiry submitted through the Microsoft Fabric Community Forum.

Kindly find attached the screenshot and PBIX file, which may assist in resolving the issue:

If you find our response helpful, we would be grateful if you could mark it as the accepted solution and provide kudos. This will be beneficial to other community members facing similar queries.

Should you have any further questions, please do not hesitate to contact the Microsoft Fabric community.

Thank you.

Re: running total goes wrong

BIuser09 — Fri, 16 May 2025 07:50:15 GMT

Bingo !! Thanks !

most DAX running total examples just show calculate without SUMX.

Re: running total goes wrong

BIuser09 — Fri, 16 May 2025 08:37:55 GMT

Before this solution, I also tried to use virtual tables to get the correct running total.

This worked out when I converted the virtual table to a real table but I was not able to transfer the result into my matrix.

I'm wondering what I did wrong here ?

See below the DAX:

TableCumBacklog = 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