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...
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.

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.