Forum Discussion
Dynamic running Total
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:
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 ) )
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!
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
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:
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
)
)
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!
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.