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 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.

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

     

12 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

      Hi,

      Below mentioned the working part,

       

  • UDMH's avatar
    UDMH
    Frequent Visitor
    1 year ago

    Hi All,

    Here is my example,

    Input data,

    Posting DateClearing DateValueDoc.id
    31-Dec-2431-Mar-251001


    Output,
    Ageing Table:

    Ageing0-3031-6061-9091
         
         

     





    if, slicer month Jan selected,

    Ageing0-3031-6061-9091
    1100   
         

     

    if, slicer month Feb selected,

    Ageing0-3031-6061-9091
    1 100  
         

     

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

    Ageing0-3031-6061-9091
         
         
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

      Hi, 

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

      Master:
      PostingDate                                    ClearingDate                        DocNo  Amt 

      Tuesday, December 31, 2024Monday, March 31, 2025202301500 
      Friday, January 31, 2025Friday, February 28, 2025202304200 
      Friday, February 28, 2025Monday, March 31, 2025202350300 
      Wednesday, December 31, 2025Wednesday, April 30, 2025202360400 

       

      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 No0-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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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