topic Re: Dynamic running Total in Desktop

Dynamic running Total

Zzzzzzz — Tue, 20 May 2025 13:00:43 GMT

Hi everyone, I'm looking for help with a DAX formula in Power BI. I want to create a dynamic running total that calculates aging by amount of days. My matrix table is set up with:

- Customer Number, Customer Name, Invoice Number, and Invoice Date in the rows section
- Aging Bucket in the columns section (note that some customers can have different aging buckets, making the aging bucket dynamic)
- AR Balance measure in the values section

I'd like to add a column called "Running Total" that calculates a running total based on the change in Invoice Number, and also takes into account the dynamic aging buckets. This column should reset itself whenever the Customer Number changes and appear to the right of the matrix table. Can anyone assist me with the DAX formula for this? I'd greatly appreciate any help!

Re: Dynamic running Total

pankajnamekar25 — Tue, 20 May 2025 13:04:41 GMT

Hello Zzzzzzz

use this measure

Running Total AR Balance =

VAR CurrentCustomer = MAX('Table'[Customer Number])

VAR CurrentInvoiceDate = MAX('Table'[Invoice Date])

VAR CurrentInvoiceNumber = MAX('Table'[Invoice Number])

RETURN

CALCULATE(

[AR Balance],

FILTER(

ALLSELECTED('Table'),

'Table'[Customer Number] = CurrentCustomer &&

(

'Table'[Invoice Date] < CurrentInvoiceDate ||

(

'Table'[Invoice Date] = CurrentInvoiceDate &&

'Table'[Invoice Number] <= CurrentInvoiceNumber

)

Thanks,
Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

Re: Dynamic running Total

Zzzzzzz — Tue, 20 May 2025 20:14:21 GMT

i dont understand in my columns section i have this formula there is no running total here how will your mesuare add a running total

Aging Bucket = SWITCH(

TRUE(),

'ardet'[LINE BALANCE] < 0, "- 0 Days (cred)",

[Days Overdue] < 0, "Not Due",

[Days Overdue] <= 30, "0-30 Days",

[Days Overdue] <= 60, "31-60 Days",

[Days Overdue] <= 90, "61-90 Days",

[Days Overdue] <= 120, "91-120 Days",

"Over 120 Days"

) and don't forget depending on how old the customer invoice dates are that happens on what column of the aging bucket it shows but i want the running total to always show at the end

Re: Dynamic running Total

Ashish_Excel — Wed, 21 May 2025 02:54:49 GMT

Hi,

Share some data to work with, explain the question and show the expected result. Share data in a format that can be pasted in an MS Excel file.

Re: Dynamic running Total

Zzzzzzz — Wed, 21 May 2025 02:56:59 GMT

Hi anyone have a solution?

Re: Dynamic running Total

Zzzzzzz — Wed, 21 May 2025 03:54:52 GMT

here is one excel example of dynamic columns

so as you can see the running total column is always to the end of the data but the aging buckets are dynamic according to the invoice date

Re: Dynamic running Total

v-aatheeque — Wed, 21 May 2025 11:14:10 GMT

Hi Zzzzzzz ,

Dynamic running total measure in Power BI that calculates the cumulative AR Balance per Customer, based on Invoice Date or Invoice Number, and takes into account the context of dynamic Aging Buckets.

can you try this measure :

Running Total AR = VAR CurrentCustomer = SELECTEDVALUE('AR Table'[Customer Number]) VAR CurrentInvoice = SELECTEDVALUE('AR Table'[Invoice Number]) RETURN CALCULATE( SUM('AR Table'[AR Balance]), FILTER( ALLSELECTED('AR Table'), 'AR Table'[Customer Number] = CurrentCustomer && 'AR Table'[Invoice Number] <= CurrentInvoice ) )

Hope this helps !

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

Re: Dynamic running Total

Zzzzzzz — Wed, 21 May 2025 17:23:27 GMT

its not working its adding a running total after each aging bucket

Re: Dynamic running Total

Ashish_Excel — Thu, 22 May 2025 00:19:32 GMT

Cannot understand anything from screenshots. Share the download link of an MS Excel file. Show your Excel formulas there. I will try to convert them into measures.

Re: Dynamic running Total

Zzzzzzz — Thu, 22 May 2025 04:02:21 GMT

I don't have any formula that work it needs to all be created I need an aging bucket formula that would have running Total only at the end and an ar balance formula that would put the correct values where it belongs

Re: Dynamic running Total

v-aatheeque — Thu, 22 May 2025 11:51:41 GMT

Hi Zzzzzzz ,

To implement a dynamic running total of AR Balances across varying aging buckets and grouped by customer and invoice number/date, we followed a structured approach in Power BI:

1.Create a Calendar Table (for time-based calculations)
We used a calendar table to support time intelligence functions like running totals:
Calendar = CALENDARAUTO()

We then established a relationship between:

Calendar[Date] and AR Table[Invoice Date]

This relationship enables accurate date-based filtering and aggregation.

2. Create the Running Total Measure (by Invoice Date)
We defined the following DAX measure to compute a running total per customer, ordered by Invoice Date:

Running Total AR = VAR CurrentCustomer = SELECTEDVALUE('AR Table'[Customer Number]) VAR MaxDate = MAX('Calendar'[Date]) RETURN CALCULATE( SUM('AR Table'[AR Balance]), FILTER( ALL('Calendar'), 'Calendar'[Date] <= MaxDate ), 'AR Table'[Customer Number] = CurrentCustomer )

This measure resets the total per customer and dynamically calculates the cumulative AR balance over time.

3. If invoice numbers are numeric and represent chronological order, you can create an alternative running total that relies on Invoice Number instead of Invoice Date:

Running Total AR by Invoice = VAR CurrentCustomer = SELECTEDVALUE('AR Table'[Customer Number]) VAR CurrentInvoice = SELECTEDVALUE('AR Table'[Invoice Number]) RETURN CALCULATE( SUM('AR Table'[AR Balance]), FILTER( ALL('AR Table'), 'AR Table'[Customer Number] = CurrentCustomer && 'AR Table'[Invoice Number] <= CurrentInvoice ) )

Use this when Invoice dates are unreliable.

You want to sort balances specifically by invoice sequence.

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

Re: Dynamic running Total

v-aatheeque — Mon, 26 May 2025 18:05:56 GMT

Hi Zzzzzzz ,

If the information is helpful, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post. If you are still facing any issue, please let us know in the comments. We are glad to help you.

We value your feedback, and it will help us to assist others who might have a similar query. Thank you for your contribution in enhancing Microsoft Fabric Community Forum.

Re: Dynamic running Total

v-aatheeque — Thu, 29 May 2025 17:18:04 GMT

Hi ZZZZZ,
we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If the response addressed by the community member for your query, please mark it as Accept Answer and click Yes if you found it helpful.

Thank You.

Re: Dynamic running Total

Zzzzzzz — Thu, 29 May 2025 17:27:26 GMT

hi i cannot share a MS excel file i think the picture is enough i basically need a running total by invoice number that will move according to the aging bucket so if the aging bucket is only two columns because the customer only has 30 days invoice then the running total will be in the fourth column

Re: Dynamic running Total

v-aatheeque — Fri, 30 May 2025 08:15:05 GMT

Thanks for the clarification. To achieve a running total by invoice number that dynamically adjusts based on the aging buckets and always appears as the last column, you can use a DAX measure like this:

This measure calculates a cumulative total of AR Balance per customer ordered by invoice number and respects your matrix’s current filter context, including dynamic aging buckets.

When you add this measure to your matrix, it will automatically appear as the last column after the aging buckets, even if the number of aging buckets varies per customer.

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

Re: Dynamic running Total

v-aatheeque — Mon, 02 Jun 2025 06:46:25 GMT

Hi Zzzzzzz ,

If the information is helpful, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post. If you are still facing any issue, please let us know in the comments. We are glad to help you.

We value your feedback, and it will help us to assist others who might have a similar query. Thank you for your contribution in enhancing Microsoft Fabric Community Forum.