AS AT slicers

[Steve_M]

Hi, I am trying to create an “AS AT” AR Aging report. I have a Live connection but I don’t have access to make changes to the underlying data model, so I am trying to do everything in DAX.

If I look at the transaction level, I have the Due Date (not linked to a dim), and a Date that is used if the due date is blank.  I use this measure to create the normal AR buckets of 1-30, 31-60, 61-90, 91+. 

To calculate the number of days open, I use the following measure, which always gives me the as-at-today result.

# 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 Second_Date =

        MAXX(

            ALLSELECTED('Transaction Date'),'Transaction Date'[*Date (trans)]

        )

    RETURN  

        DATEDIFF(

            First_Date,

            IF(Second_Date > TODAY(),

                TODAY(),

                Second_Date

            ),

            DAY

        )

I have a slicer for the user to select an as-at-date, but I don’t want this slicer to filter the transactions otherwise i don't pick up all transactions with an open balance. The date dimension and the transactions line table have a one-to-many relationship.

This measure's result is that it always calculates the second date, such as Today(), as the MAXX always returns the max of the Transaction date table.

How do I go about getting the date selected without that date filtering the table,

Thanks

Steve   

[v-achippa]

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

[danextian]

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

Sample pbix and tutorial on YouTube.

[Kedar_Pande]

@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

[Samarth_18]

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]

Thanks Samarth, I still get the same result.  Below is what I am trying to achieve; when the date selected is, say, the 28th Nov, I want to calculate using that date.  If the date selected is 31st Dec, which is greater than today, then use today's date.  You can see in the debug slicer that the code to calculate the second date is working, but when it is used in the visual with filtering turned off, the MAX returns the max of the date table and not the date selected.  I don't even know if this is possible.  Working with a Live connection is making it hard but it what I have to deal with

[danextian · Accepted solution]

Hi @Steve_M 

As mentioned in  my previous reply, filters coming from a related table will affect what records are visible.  So if you select 28/11, you will see only the 28/11 rows. Functions such as ALL, ALLSELECTED, REMOVEFILTERS etc affect the results of a measure but cannot unhide the non-selected rows.  Change your slicer to before if you want to also show rows with dates prior to the currently selected.

[Steve_M]

Thank you, I think this will work with Samarth's code.