Forum Discussion

Sammie_K's avatar
Sammie_K
New Member
1 year ago
Solved

Dynamic column

I am working on an aging report. I would like to have a column in my table with due/outstanding days. These days should change based on a selection in the report. e.g, when someone selects an earlier date, the days reduce and vice versa

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Sammie_K 

     

    Here I create a set of sample:

    Table:

    Then add 3 measures:

    Date = DATE(2024,9,8) //you can change the input date by edit the measure
    Diff =
DATEDIFF ( MAX ( 'Table'[Date] ), 'Table1'[Date], DAY )
//Calculate how many days apart the two dates are
    range =
IF (
    'Table1'[Diff] < 31,
    "<30 Days",
    IF (
        'Table1'[Diff] > 30
            && 'Table1'[Diff] < 61,
        "31-60 Days",
        IF (
            'Table1'[Diff] > 60
                && 'Table1'[Diff] < 91,
            "61-90 Days",
            IF ( 'Table1'[Diff] > 90, ">90 Days" )
        )
    )
)

    The result is as follow:

     

     

    Best Regards

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

4 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    Hi Sammie_K ,

     

    To generate a dynamic aging report as described, you will need transaction data from the Accounts Receivable module of your ERP system. This data should include:

    • Amounts (representing sales, collections, credit notes, etc.)
    • Posting date
    • Document number (representing the invoice number)
    • Customer name (or customer number)
    • Payment term

    With this data, you can create an aging bucket dynamically using a measure instead of a calculated column. This approach is necessary because your data reflects not just the aging balance as of a particular date, but encompasses all transactions over time. This allows you to view the Accounts Receivable aging as of any date you select.

     

    If the data you have only includes the AR balance as of a specific date, you won't be able to create a dynamic aging analysis for any date. This is because some accounts receivable may have already been cleared by that point, and some sales may not have been recognized yet. Therefore, such data will not accurately reflect the full picture of AR aging across different dates.

     

    Best regards,

    • Sammie_K's avatar
      Sammie_K
      New Member
      1 year ago

      Thanks DataNinja777 

       

      See the screenshot below, I have created a sample of what I need. Now in excel, I would simply change the date in F11, then the due days in the rows below would change. I would then create a conditional column with the Nested IF formula with aging categories that I can later pivot and thus create a summarized aging report.

       

      I am new to BI, but I can do the conditional column, and my data has the parameters you mention above. My request is "how can I have the due days in column F based on the input in F11, an input I will keep changing from time to time." If it is a dynamic measure that will help me, I would be very happy to learn it.

       

         

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Sammie_K 

         

        Here I create a set of sample:

        Table:

        Then add 3 measures:

        Date = DATE(2024,9,8) //you can change the input date by edit the measure
        Diff =
DATEDIFF ( MAX ( 'Table'[Date] ), 'Table1'[Date], DAY )
//Calculate how many days apart the two dates are
        range =
IF (
    'Table1'[Diff] < 31,
    "<30 Days",
    IF (
        'Table1'[Diff] > 30
            && 'Table1'[Diff] < 61,
        "31-60 Days",
        IF (
            'Table1'[Diff] > 60
                && 'Table1'[Diff] < 91,
            "61-90 Days",
            IF ( 'Table1'[Diff] > 90, ">90 Days" )
        )
    )
)

        The result is as follow:

         

         

        Best Regards

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