topic Re: Running totals per quarter and year in DAX Commands and Tips

Running totals per quarter and year

EllenHeijmans1 — Thu, 29 Sep 2022 14:36:38 GMT

I need a running total per quarter and year. I've found and expression that helped me to create a measure to calculate a running total per quarter.

Running total quarterly =

CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[Quarter]<=MAX('Table'[Quarter])))

But than this is the result:

as you can see, the totals for 2022 are the same as for 2020. This is because of the fact that the measure only takes the quarter into account.

What I want is that the last to lines shows a total of 117 and 118. So I changed the measure as follows:

Running total quarterly and yearly =

CALCULATE(

SUM('Table'[amount]),

FILTER(

ALL('Table'),

'Table'[Quarter]<=MAX('Table'[Quarter]) && 'Table'[Year]<=2022

)

But it has the same result:

How can I display running totals based on quarters over the years?

Re: Running totals per quarter and year

Greg_Deckler — Thu, 29 Sep 2022 14:37:19 GMT

EllenHeijmans1 Better Running Total - Microsoft Power BI Community

Re: Running totals per quarter and year

EllenHeijmans1 — Fri, 30 Sep 2022 06:29:58 GMT

Hi Greg,

Thanks for your answer. But unfortunately it doesn't solve my problem.

When I use your example I get a beautifull running total, but based on amount and not on a amount per quater. I need a running total per quarter over time. See the image below. So it needs to go from 34, to 58, 79, 106

111, 112.

Re: Running totals per quarter and year

Anonymous — Fri, 30 Sep 2022 07:18:19 GMT

Hi EllenHeijmans1 ,

I think you can use YearQuarter as the filter key in your measure. You can try this code to achieve your goal.

Running total quarterly and yearly = VAR _YearQuarter = SELECTEDVALUE ( 'Table'[Year] ) * 100 + SELECTEDVALUE ( 'Table'[Quarter] ) RETURN CALCULATE ( SUM ( 'Table'[amount] ), FILTER ( ALL ( 'Table' ), 'Table'[Year] * 100 + 'Table'[Quarter] <= _YearQuarter ) )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Running totals per quarter and year

EllenHeijmans1 — Fri, 30 Sep 2022 10:00:37 GMT

Yes!! That works. Thanks Rico.