Dynamic column

[Sammie_K]

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

[DataNinja777]

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]

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 · Accepted solution]

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.

[Ritaf1983]

Hi @Sammie_K 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly