Dynamic Aging based on Posting and Clearing Date

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