topic Re: Better Running Total in Quick Measures Gallery

Better Running Total

Greg_Deckler — Sun, 09 Oct 2022 12:14:24 GMT

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

Re: Better Running Total

cabc_xlorla — Sat, 10 Sep 2022 01:57:34 GMT

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.

Re: Better Running Total

rosiebilton23 — Mon, 30 Oct 2023 02:31:53 GMT

Thanks so much! I have been struggling with this measure for what feels like dayssss!

Re: Better Running Total

Anonymous — Tue, 07 May 2024 13:44:49 GMT

Greg_Deckler Thank you for this file
I just added one extra column DAY. I placed on page 2 of the attached file. and Total is not correct.
I am facing same error on my main file. Your support will be great.

Re: Better Running Total

Greg_Deckler — Tue, 07 May 2024 14:30:31 GMT

Anonymous See reply in original thread.