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

     

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