AS AT slicers

Hi Steve_M ,

You could try below code:-

# 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 Selected_Date = 
    CALCULATE(
        MAX('Transaction Date'[*Date (trans)]), 
        ALLSELECTED('Transaction Date')
    )

VAR Second_Date = 
    IF(
        Selected_Date > TODAY(),
        TODAY(),
        Selected_Date
    )

RETURN
    DATEDIFF(First_Date, Second_Date, DAY)

It would be very helpful if you could share the sample data and the required output to answer more specifically.

Steve_M 

Updated Measure for # Days Open AR:

# 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 Selected_AsAtDate =
SELECTEDVALUE('Transaction Date'[*Date (trans)], TODAY())

RETURN
DATEDIFF(
First_Date,
MIN(Selected_AsAtDate, TODAY()), 
DAY
)

💌If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

Hi Steve_M 

The simplest method for transactions not to be fitlered out is to use "before" slicer and cover the start date with a shape. If you  are using a live connection and there isn't a disconnected dates table in the model, you'll be limited to this. You will have more flexbility with connecting to a semantic model via direct query (if enabled). While you can use measures to modify the filter context and show values outside the selected range,  a related dates table will show only the rows that's been selected.

Here's the demo- https://community.fabric.microsoft.com/t5/Data-Stories-Gallery/Accounts-Receivable-Aging-Schedule/td-p/3960847 

Sample pbix and tutorial on YouTube.

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