cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

@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

2 REPLIES 2
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
Super User
Super User

@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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.