Forum Discussion

UDMH's avatar
UDMH
Frequent Visitor
1 year ago
Solved

Dynamic Aging based on Posting and Clearing Date

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 sh...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

grazitti_sapna's avatar
grazitti_sapna
Super User
1 year ago

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's avatar
UDMH
Frequent Visitor
1 year ago

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