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

AR aging report bucket and calculation

Hi there,

 

I have made a SQL query and am pulling in the data from our ERP with PowerBI's DirectQuery function.  The output looks like this:

formerlyknownas_2-1669148489716.png

 

The OverDueDays column is the number of days the document is overdue.  This number considers the payment terms and is relative to the RefDate field.  This means anything that is less than 0 is considered Future Remit (receivables not past the due date).  

 

I'm looking to make a AR Aging Report with buckets: Future Remit, 0-30, 31-60, 61-90, 91-120, 120+ where the balance due is displayed in a matrix where I can group on either the employee or the company.  I have thought about making the buckets in the SQL query, but as I'm learning more about Power BI, I'd like see what's possible here.  I've not found a forum topic that provides a clear answer on such an approach, so I'd be most appreciative of any ideas on how to accomplish this.

 

Many thanks in advance.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@formerlyknownas , You can create a new column like

 

Switch( true(),

[Over Due days]<= 30 , "   < 30",

[Over Due days]<= 60 , "  31 -60",

[Over Due days]<= 90 , "  61-90",

[Over Due days]<= 120 , "  91-120",

"> 120"

)

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@formerlyknownas , You can create a new column like

 

Switch( true(),

[Over Due days]<= 30 , "   < 30",

[Over Due days]<= 60 , "  31 -60",

[Over Due days]<= 90 , "  61-90",

[Over Due days]<= 120 , "  91-120",

"> 120"

)

Thanks @amitchandak, I'm up and running.   I took that approach and did the following 

 

SWITCH(
TRUE(),
[OverDueDays] < 0, "Future Remit",
[OverDueDays] >= 0 && [OverDueDays] < 30, "0-30 days",
[OverDueDays] >= 30 && [OverDueDays] < 60, "30-60 days",
[OverDueDays] >= 60 && [OverDueDays] < 90, "60-90 days",
[OverDueDays] >= 90 && [OverDueDays] < 120, "90-120 days",
[OverDueDays] >= 120, "120+ days"
)

 

When I use this in a the matrix display, the columns are displayed A-Z, so Future Remit appears at the end and 120+ days appears after 0-30 days.  I need to find a way to customize the order of the column in the matrix, so that it appears in the order as seen above which is perhaps another topic.

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.