Forum Discussion

Zzzzzzz's avatar
Zzzzzzz
Frequent Visitor
1 year ago
Solved

Dynamic running Total

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!

Need Help
  • v-aatheeque's avatar
    v-aatheeque
    1 year ago

    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!

     

15 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    1 year ago

    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.

    • Zzzzzzz's avatar
      Zzzzzzz
      Frequent Visitor
      1 year ago

      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 
  • Ashish_Excel's avatar
    Ashish_Excel
    Solution Supplier
    1 year ago

    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.

    • Zzzzzzz's avatar
      Zzzzzzz
      Frequent Visitor
      1 year ago

      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 

       

      • Ashish_Excel's avatar
        Ashish_Excel
        Solution Supplier
        1 year ago

        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.

    • v-aatheeque's avatar
      v-aatheeque
      Community Support
      1 year ago

      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!

      • Zzzzzzz's avatar
        Zzzzzzz
        Frequent Visitor
        1 year ago

        hi

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