Running Total in Matrix with Multiple Rows

[Brotedo]

I have a matrix in PowerBI with two row filters (name and team) and columns as 12 dates starting from current month. I have a measure that I display for each month, but I want it to display as a running total rather than just as the value for each month.

This is the code I'm currently using:

Rolling 12 Month = calculate([12 Month],filter(all(Planning[Month]),Planning[Month]<=max(Planning[Month])))

    

where Planning[Month] defines the column header and 12 Month is this measure:

   12 Month = sum([12MonthK])

where [12MonthK] is just a column of values.

The only code I've gotten to actually create a running total is this:

RTTest = If( Not ISEMPTY(Planning),      VAR _maxDate = max(Planning[Month])    Return     calculate(  [YTD 12 Month], Planning[Month]<=_maxDate, all(Planning)))   

but this running total just completely ignores any filters (the running total is the same for each name/team combo).

I tried using allexcept in the last bit of code, but it just immediately stopped being a running total.

It currently looks like this:

You can see clearly March to April that this is not a running total.

[v-chenwuz-msft · Accepted solution]

Hi @Brotedo ,

Can you try ALLSELECTED() to do this. Before this, you need create a dim_date table for your fact table. Use the values() to create the dim table which likes the below.

date = VALUES('Planning'[Month]) 

Then create a mesure to calculate.

c =
CALCULATE (
    SUM ( 'Table'[Values] ),
    FILTER ( ALLSELECTED ( 'date'[Month] ), [Month] <= MAX ( 'date'[Month] ) )
)

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[daXtreme]

Hi @Brotedo 

If you want to create a running total, you have to have a good model. Currently your model does not look to be good. A good model is a star-schema with fact tables and dimensions. Please consult THIS. If you build a proper Date Table, it'll be rather easy to create a RT. There are myriads of posts on this forum about how to get a RT right (once the model is correct). Please use the search box at the top of the page to find them. Also, you can check YT videos about RT by Alberto Ferrari and Marco Russo. Also, you can look HERE.

Hope this helps 🙂