Skip to main content
cancel
Showing results for 
Search instead 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

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

Aging Buckets PowerBI.png

1 ACCEPTED SOLUTION
ebeery
Solution Sage
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

View solution in original post

3 REPLIES 3
jhauff_RFO
Frequent Visitor

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

 

Age Bucket.jpg

Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
ebeery
Solution Sage
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

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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