AS AT slicers

Hi Steve_M 

Thank you for reaching out to Microsoft Fabric Community.

To address your issue, you need to ensure that the "As-At Date" slicer works without filtering the transaction lines table directly but still allows you to calculate the number of days open relative to the selected "As-At Date."

Here's how you can achieve it by using this below DAX query:

# Days Open AR =

VAR First_Date =

    IF(

        ISBLANK(MAX('Transaction Lines'[Due Date/Receive By])),

        MAX('Transaction Lines'[Date]),

        MAX('Transaction Lines'[Due Date/Receive By])

    )

VAR AsAtDate =

    SELECTEDVALUE('Date Dimension'[Date], TODAY()) -- Get the selected date or default to TODAY()

RETURN

    DATEDIFF(

        First_Date,

        IF(AsAtDate > TODAY(), TODAY(), AsAtDate), -- Ensure the date doesn't exceed today's date

        DAY

    )

• Here the SELECTEDVALUE retrieves the date selected by the user from the slicer.
• If no date is selected, it defaults to TODAY().
• If the selected "as-at" date is in the future, it defaults to today's date to avoid discrepancies.
• The slicer does not filter out any transactions, ensuring accurate results.

This approach should meet your requirements and ensure accurate AR aging calculations for any selected date. Let me know if you need any further adjustments!

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Thanks and Regards,

Anjan Kumar Chippa