Dynamic Aging based on Posting and Clearing Date

[UDMH]

Hi,

I have a calendar table

Along with that, there is a table with Posting date, Clearing Date and Value, created relationship between Posting date and date in the calendar table.

Need to show aging as,

For example, my posting date is 31-Dec-2024 and Clearing date is 31-Mar-2025,

And if I select Jan, the value should be under 0-30 days ageing, on selecting Feb, 31-60 days, and so on. After Mar, it is cleared, so on selecting Apr, it should be blank.

[v-pgoloju]

Hi UMDH

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @UDMH.

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @UDMH,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[v-pgoloju · Accepted solution]

Hi @UDMH,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Try Using below DAX logic.

calculated column
Ageing Bucket =
VAR CurrentMonth = MAX('Calendar'[Date])
VAR PostingDate = 'Table'[Posting Date]
VAR ClearingDate = 'Table'[Clearing Date]
VAR AgingDays = DATEDIFF(PostingDate, CurrentMonth, DAY)

RETURN
SWITCH(
TRUE(),
AgingDays <= 30 && CurrentMonth >= PostingDate && CurrentMonth <= ClearingDate, "0-30 Days",
AgingDays <= 60 && CurrentMonth > PostingDate && CurrentMonth <= ClearingDate, "31-60 Days",
AgingDays <= 90 && CurrentMonth > PostingDate && CurrentMonth <= ClearingDate, "61-90 Days",
AgingDays > 90 && CurrentMonth > PostingDate && CurrentMonth <= ClearingDate, "91+ Days",
BLANK()
)

Measure

Amount in Aging Bucket =
VAR _SelectedDate = MAX('Calendar'[Date])
VAR _PostingDate = MAX('Table'[Posting Date])
VAR _ClearingDate = MAX('Table'[Clearing Date])

VAR AgingDays = DATEDIFF(_PostingDate, _SelectedDate, DAY)

RETURN
IF (
NOT ISBLANK(_ClearingDate) && _SelectedDate > _ClearingDate,
BLANK(),
SWITCH(
TRUE(),
AgingDays <= 30 && _SelectedDate >= _PostingDate && _SelectedDate <= _ClearingDate, SUM('Table'[Amount]),
AgingDays <= 60 && _SelectedDate > _PostingDate && _SelectedDate <= _ClearingDate, SUM('Table'[Amount]),
AgingDays <= 90 && _SelectedDate > _PostingDate && _SelectedDate <= _ClearingDate, SUM('Table'[Amount]),
AgingDays > 90 && _SelectedDate > _PostingDate && _SelectedDate <= _ClearingDate, SUM('Table'[Amount]),
BLANK()
)
)

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thank you & regards,
Prasanna Kumar

[v-pgoloju]

Hi @UDMH,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Thanks & Regards,

Prasanna Kumar

[UDMH]

Hi, 

Please find the details.
I have two table, Master table and Calendar table

Master:
PostingDate                                    ClearingDate                        DocNo  Amt 

Tuesday, December 31, 2024	Monday, March 31, 2025	202301	500
Friday, January 31, 2025	Friday, February 28, 2025	202304	200
Friday, February 28, 2025	Monday, March 31, 2025	202350	300
Wednesday, December 31, 2025	Wednesday, April 30, 2025	202360	400

Calendar = 

CALENDAR(MIN('Table'[Posting Date]),MAX('Table'[Clearing Date]))

Used caculated column and measure,

Calculated Column - for ageing calculation:

Ageing Bucket =

VAR CurrentMonth = MAX('Calendar'[Date])

VAR PostingMonth = MONTH('Table'[Posting Date])

VAR ExpiryMonth = MONTH('Table'[Clearing Date])

VAR StartDate ='Table'[Posting Date]

VAR EndDate = 'Table'[Clearing Date]

VAR AgingDays = DATEDIFF(StartDate, CurrentMonth, DAY)

RETURN

    SWITCH(

        TRUE(),

        AgingDays <= 30, "0-30 Days",

        AgingDays <= 60, "31-60 Days",

        AgingDays <= 90, "61-90 Days",

        AgingDays > 90, "91+ Days",

        BLANK()

    )

Calculated Measure:

Amount in Aging Bucket =

VAR _SelectedDate = MAX('Calendar'[Date])

VAR _PostingDate = MAX('Table'[Posting Date])

VAR _ClearingDate = MAX('Table'[Clearing Date])

RETURN

IF(

NOT ISBLANK(_ClearingDate) && _SelectedDate > _ClearingDate,

BLANK(),

SUM('Table'[Amount])

)

Slicerused from -> calendar table

On selecting 2025 Jan, it is showing as

Expected output,

Document No	0-30 days
202304		200

Based on the dynamic selection, the ageing should appear.
For example, as of Jan 2025, it is coming under 0-30 days, but it is calculating overall ageing based on Posting Date as 61-90days.

On selecting Feb 2025, it sholud come under 31-60 days
On selecting Mar 2025, the value is cleared, so it should return blank.


Thank you in advance

[UDMH]

Hi All,

Here is my example,

Input data,

Posting Date	Clearing Date	Value	Doc.id
31-Dec-24	31-Mar-25	100	1

Output,
Ageing Table:

Ageing	0-30	31-60	61-90	91

if, slicer month Jan selected,

Ageing	0-30	31-60	61-90	91
1	100

if, slicer month Feb selected,

Ageing	0-30	31-60	61-90	91
1		100

if, slicer month Apr selected, the document is cleared, so blank

Ageing	0-30	31-60	61-90	91

[v-pgoloju]

Hi @UDMH,

Just following up to check if the solution shared by our Super User helped resolve your issue. If you're still facing difficulties or need further assistance, please let us know with sample data and expected output,  we’re here to help!

If the response addressed your query, we kindly request you to mark it as Accepted Solution and click Yes if you found it helpful. This supports others in the community as well.

Best regards,

Prasanna Kumar

[UDMH]

Hi,

Below mentioned the working part,

[danextian]

Hi @UDMH 

Please see this demo

https://community.fabric.microsoft.com/t5/Data-Stories-Gallery/Accounts-Receivable-Aging-Schedule/td...

There's a link to the tutorial and sample pbix in the description.

[grazitti_sapna]

Hi @UDMH,

For creating aging bucket first create a relationship between date table and Transaction table 

Relationship: Calendar[Date] → Transaction[Posting Date]

Create Aging bucket measure

Aging Bucket =
VAR _SelectedDate = MAX('Calendar'[Date])
VAR _PostingDate = MAX('Transaction'[Posting Date])
VAR _ClearingDate = MAX('Transaction'[Clearing Date])
VAR _DaysOpen = DATEDIFF(_PostingDate, _SelectedDate, DAY)

RETURN
IF (
NOT ISBLANK(_ClearingDate) && _SelectedDate > _ClearingDate,
BLANK(), -- Cleared already, don't show
SWITCH(
TRUE(),
_DaysOpen <= 30, "0-30 Days",
_DaysOpen <= 60, "31-60 Days",
_DaysOpen <= 90, "61-90 Days",
_DaysOpen <= 120, "91-120 Days",
">120 Days"
)
)

Then create measure for aging value to show

Aging Value =
VAR _SelectedDate = MAX('Calendar'[Date])
VAR _PostingDate = MAX('Transaction'[Posting Date])
VAR _ClearingDate = MAX('Transaction'[Clearing Date])

RETURN
IF(
NOT ISBLANK(_ClearingDate) && _SelectedDate > _ClearingDate,
BLANK(), -- cleared, do not show value
SUM('Transaction'[Value])
)

Create visual:-

• Axis = Aging Bucket

• Values = Aging Value

• Use the Calendar slicer for Month/Year selection.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[UDMH]

Hi @grazitti_sapna,

The aging bucket is not changing, it is showing 0-30 days and for rest of month showing blank.

Since, relationship is created, it is showing only the selected month, the rest of month balnk.

Thank You