Better Running Total 09-08-2022 06:27 AM - last edited 10-09-2022 05:14 AM

[Greg_Deckler]

Microsoft's running total quick measure, well, it's just not very good. It's overly complex and doesn't work in single table situations. There's a better way as shown in this video: MSHGQM - Don't Use CALCULATE! - YouTube

For reference, Microsoft's running total quick measure generates code such as:

Value running total in Date = 
CALCULATE(
	SUM('Table'[Value]),
	FILTER(
		ALLSELECTED('Table'[Date]),
		ISONORAFTER('Table'[Date], MAX('Table'[Date]), DESC)
	)
)

As shown, this running total doesn't work in single table situations. A better, less complex way to create a running total that works with a single table is like this:

Better RT = 
    VAR __Date = MAX('Table'[Date])
    VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date)
RETURN
    SUMX(__Table,[Value])

And with just a minor change, this method also works if you have a separate Dates table:

Better RT 2 = 
    VAR __Date = MAX('Dates'[Date])
    VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date)
RETURN
    SUMX(__Table,[Value])

Watch the video!

eyJrIjoiMTcxZWJiZmEtZDdiMy00YWYyLWEyOTYtMmI1MDQ4YjlmMTY5IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[cabc_xlorla]

Thanks for sharing the pbix file Greg! I vouch for version Better RT 2, since it's rare for me working in Power BI without a data model.