Running totals per quarter and year

[EllenHeijmans1]

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?

[Greg_Deckler]

@EllenHeijmans1 Better Running Total - Microsoft Power BI Community

[EllenHeijmans1]

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.

[v-rzhou-msft · Accepted solution]

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.

[EllenHeijmans1]

Yes!! That works. Thanks Rico.