cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## Creating an Aging Bucket

Hello all,

I am new to PowerBI and am looking to create an AR Aging bucket. I have pulled all the relevant information from our SQL server, but cannot figure out how to create calculate columns based on the net_due_date field.

We are looking to create 30, 60, 90, and over 90 day aging buckets for reporting that would pull the amount_remaining field to the appropriate buckets based on age.

What would the formulas look like to create these buckets?

Below are the current fields that I have for the table.

1 ACCEPTED SOLUTION
Solution Sage

@Celliott04 what is your definition for "age"?  If it is net_due_date - invoice_date, then a calculated columns something like below might work:

``````Age_Bucket =
VAR _Age = DATEDIFF('Table'[invoice_date],'Table'[net_due_Date], DAY)
VAR _Result =
SWITCH(
TRUE(),
_Age < 30, "0-30 days",
_Age >= 30 && _Age < 60, "30-60 days",
_Age >= 60 && _Age < 90, "60-90 days",
_Age >= 90, "90+ days"
)
Return
_Result``````
3 REPLIES 3
Frequent Visitor

ebeery, Thanks so much for the Age_Bucket code. It works great!

Super User

Hi,

We can use the CALCULATE() and FILTER() functions in a calculated column formula to get your desired result.  I can offer more help if you share the link from where i can download your PBI file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Solution Sage

@Celliott04 what is your definition for "age"?  If it is net_due_date - invoice_date, then a calculated columns something like below might work:

``````Age_Bucket =
VAR _Age = DATEDIFF('Table'[invoice_date],'Table'[net_due_Date], DAY)
VAR _Result =
SWITCH(
TRUE(),
_Age < 30, "0-30 days",
_Age >= 30 && _Age < 60, "30-60 days",
_Age >= 60 && _Age < 90, "60-90 days",
_Age >= 90, "90+ days"
)
Return
_Result``````

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors