Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.