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
kjrudy14
Frequent Visitor

Dynamic Date in AR Aging Report

Hello,

 

I am currently working on an AR Aging report in Power BI.  I have the report completed, however, I am unable to make the aging buckets dynamic with a date selector.    The data is only as of Today's date...

 

Here are a few of the calculations :

 

Days Aged = INT(max('Calendar Table'[Date])-max('AR Data'[Due Date]))    -->Calculates Days Aged
 
Calendar Table = CALENDAR(min('AR Data'[Due Date]),today())       -->Calendar Table 
 
Age 1-30 = CALCULATE(sum('AR Data'[Remaining Balance]), filter(ALL('AR Data'[Remaining Balance]), [Days Aged] >= 1 && [Days Aged] < 31))       -->Aging Band 1-30
 
Age 31-60 = CALCULATE(sum('AR Data'[Remaining Balance]), filter(all('AR Data'[Remaining Balance]), [Days Aged] > 30 && [Days Aged] < 61))    -->Aging Band 31-60 
 
The rest of the bands follow the same logic...
 
Any advice?
 
 
 
 
4 REPLIES 4
faisalazh
Advocate II
Advocate II

I think you have to create a summary table to cross join your calendar and aging group. then create column to summarize the amounts then create relationship between new summary table with calendar.

1. cross join your calendar with aging group you can do formula like:

Summary AR = CrossJoin('CalendarTable', 'Aging Group Table')

2. Create in New Summary AR table a calculated columns. eg. Age 1-30, etc.

3. Create relationship 'Summary AR' table with 'CalendarTable'.

hope this help.

 

v-xiaotang
Community Support
Community Support

Hi @kjrudy14 

Could you share some sample data or your sample file?  So that I can write specific measure for your sample file.

-
1. create a measure to get the selected date in slcier,

selected_date= SELECTEDVALUE('Calendar Table'[Date])

2. then get the calculating date period by using the selected date,

e.g. var _startDate= selected_date-31

3. calculate sum

Age 1-30 = CALCULATE(sum('AR Data'[Remaining Balance]), filter(ALL('AR Data'[Remaining Balance]), your date period))

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

kjrudy14
Frequent Visitor

Hello,

 

The report is currently laid out like below by Customer :

 

kjrudy14_0-1632400232156.png

 

I just want to be able to add a date slicer and look at the aging report as of any particular date.

amitchandak
Super User
Super User

@kjrudy14 , do you want an aging bucket as a dimension/filter?

 

That needs dynamic segmentation with a group by says Invoice no.  And independent bucket table

 

refer

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

 

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.