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!
