Forum Discussion

tdixon61's avatar
tdixon61
Regular Visitor
4 years ago
Solved

Accounts Receivable Aging based on Transaction Report

Ive been researching this for a while now, and testing possible solutions, but cant quite get where I need my report to be. Problem: I have an invoice history report that shows the following data:...
Need Help
Tips and Tricks
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi， tdixon61 ；

    In my test, first of all, I may not know what your measure is, but if you only want to display the table that meets the conditions, you can change measure to 1 and then apply it with filter:

    OpenBalance = 
VAR SelectedDate =
    IF ( HASONEVALUE ( DateTable[ClosedDate] ), VALUES ( DateTable[ClosedDate] ), BLANK () )
RETURN
    IF (
        MAX ( SalesTable[InvoiceDate] ) <= SelectedDate
            && (
                MIN ( SalesTable[ClosedDate] ) > SelectedDate
                    || MIN ( SalesTable[ClosedDate] ) = BLANK ()
            ),
        1,
        BLANK ()
    )

    then apply it into table filter.

     The final output is shown below:

    If i select "2018-1-19"

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi， tdixon61 ；

In my test, first of all, I may not know what your measure is, but if you only want to display the table that meets the conditions, you can change measure to 1 and then apply it with filter:

OpenBalance = 
VAR SelectedDate =
    IF ( HASONEVALUE ( DateTable[ClosedDate] ), VALUES ( DateTable[ClosedDate] ), BLANK () )
RETURN
    IF (
        MAX ( SalesTable[InvoiceDate] ) <= SelectedDate
            && (
                MIN ( SalesTable[ClosedDate] ) > SelectedDate
                    || MIN ( SalesTable[ClosedDate] ) = BLANK ()
            ),
        1,
        BLANK ()
    )

then apply it into table filter.

 The final output is shown below:

If i select "2018-1-19"

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • tdixon61's avatar
    tdixon61
    Regular Visitor
    4 years ago

    THANK YOU!!!! this worked! All I needed to do was remove the relationship between the dates table and the sales table...