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