Solved: Accounts Receivable Aging based on Transaction Rep...

tdixon61 · ‎12-13-2021

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:

1. Invoice Date

2. Posting Date

3. Customer ID

4. Closing/Paid Date

5. Amount

6. Due Date

I need a measure(s) that will output the Total Open AR and show the age of each open invoice based on a date selected by the viewer. So basically if I select 9/16/2021 as the "aging date", Power BI would show all invoices open as of that date. Any invoices generated prior to that date and paid after it should show open.

Im close. The measure I have written is the following:

OpenBalance =

VAR SelectedDate =

IF(HASONEVALUE(DateTable[Date]), VALUES(DateTable[Date]),BLANK())

Return

IF(MAX(SalesTable[InvoiceDate])<=SelectedDate &&

(MIN(SalesTable[ClosedDate])>SelectedDate || MIN(SalesTable[ClosedDate]) = BLANK()), MeasuresTable[TotalAmount], BLANK())

BUT it only outputs the invoices open as of the day selected (if any) that would be open as of that date. If I select 3/31/2020, it would only show the invoices generated on 3/31/2021 that were open as of that date.

Below is a sample data set that I am using.

InvoiceDate PostingDate Invoice ProductID ID Amount DueDate ClosedDate
1/31/2017 1/31/2017 100001 DH-1 10000-S1 119 3/2/2017 2/28/2017
1/31/2017 1/31/2017 100002 DH-1 20000-S1 119 3/2/2017 2/28/2017
1/31/2017 1/31/2017 100003 AS-1 30000-S1 139 3/2/2017 2/28/2017
1/31/2017 1/31/2017 100004 AS-1 40000-S1 139 3/2/2017 3/15/2017
1/31/2017 1/31/2017 100005 AS-1 50000-S1 139 3/2/2017 3/12/2017
2/28/2017 2/28/2017 100173 DH-1 10000-S1 119 3/30/2017 3/26/2017
2/28/2017 2/28/2017 100174 DH-1 20000-S1 119 3/30/2017 5/23/2017
2/28/2017 2/28/2017 100175 AS-1 30000-S1 139 3/30/2017 3/26/2017
2/28/2017 2/28/2017 100176 AS-1 40000-S1 139 3/30/2017 5/31/2017
2/28/2017 2/28/2017 100177 AS-1 50000-S1 139 3/30/2017 6/1/2017
3/31/2017 3/31/2017 100345 DH-1 10000-S1 119 4/30/2017 4/26/2017
3/31/2017 3/31/2017 100346 DH-1 20000-S1 119 4/30/2017 7/7/2017
3/31/2017 3/31/2017 100347 AS-1 30000-S1 139 4/30/2017 4/26/2017
3/31/2017 3/31/2017 100348 AS-1 40000-S1 139 4/30/2017 5/15/2017
3/31/2017 3/31/2017 100349 AS-1 50000-S1 139 4/30/2017 5/26/2017
4/30/2017 4/30/2017 100517 DH-1 10000-S1 119 5/30/2017 5/26/2017
4/30/2017 4/30/2017 100518 DH-1 20000-S1 119 5/30/2017 6/22/2017
4/30/2017 4/30/2017 100519 AS-1 30000-S1 139 5/30/2017 5/26/2017
4/30/2017 4/30/2017 100520 AS-1 40000-S1 139 5/30/2017 8/1/2017
4/30/2017 4/30/2017 100521 AS-1 50000-S1 139 5/30/2017 9/21/2017
5/31/2017 5/31/2017 100689 DH-1 10000-S1 119 6/30/2017 6/26/2017
5/31/2017 5/31/2017 100690 DH-1 20000-S1 119 6/30/2017 7/2/2017
5/31/2017 5/31/2017 100691 AS-1 30000-S1 139 6/30/2017 6/26/2017
5/31/2017 5/31/2017 100692 AS-1 40000-S1 139 6/30/2017 8/2/2017
5/31/2017 5/31/2017 100693 AS-1 50000-S1 139 6/30/2017 9/10/2017
6/30/2017 6/30/2017 100861 DH-1 10000-S1 119 7/30/2017 7/26/2017
6/30/2017 6/30/2017 100862 DH-1 20000-S1 119 7/30/2017 8/3/2017
6/30/2017 6/30/2017 100863 AS-1 30000-S1 139 7/30/2017 7/26/2017
6/30/2017 6/30/2017 100864 AS-1 40000-S1 139 7/30/2017 11/17/2017
6/30/2017 6/30/2017 100865 AS-1 50000-S1 139 7/30/2017 9/12/2017
6/30/2017 6/30/2017 100866 AS-1 60000-S1 139 7/30/2017 10/9/2017
7/31/2017 7/31/2017 101033 DH-1 10000-S1 119 8/30/2017 8/26/2017
7/31/2017 7/31/2017 101034 DH-1 20000-S1 119 8/30/2017 10/15/2017
7/31/2017 7/31/2017 101035 AS-1 30000-S1 139 8/30/2017 8/26/2017
7/31/2017 7/31/2017 101036 AS-1 40000-S1 139 8/30/2017 10/19/2017
7/31/2017 7/31/2017 101037 AS-1 50000-S1 139 8/30/2017 10/3/2017
7/31/2017 7/31/2017 101038 AS-1 60000-S1 139 8/30/2017 12/10/2017
7/31/2017 7/31/2017 101039 DH-1 70000-C1 119 8/30/2017 9/4/2017
7/31/2017 7/31/2017 101047 DH-1 70000-C2 119 8/30/2017 9/4/2017
7/31/2017 7/31/2017 101048 DH-1 70000-C3 119 8/30/2017 9/4/2017
7/31/2017 7/31/2017 101049 DH-1 70000-C4 119 8/30/2017 9/4/2017
7/31/2017 7/31/2017 101050 DH-1 70000-C5 119 8/30/2017 9/4/2017
7/31/2017 7/31/2017 101055 AS-1 80000-C1 139 8/30/2017 9/4/2017
7/31/2017 7/31/2017 101065 AS-1 80000-C2 139 8/30/2017 9/4/2017
7/31/2017 7/31/2017 101066 AS-1 80000-C3 139 8/30/2017 9/4/2017
7/31/2017 7/31/2017 101067 AS-1 80000-C4 139 8/30/2017 9/4/2017
7/31/2017 7/31/2017 101068 AS-1 80000-C5 139 8/30/2017 9/4/2017
8/31/2017 8/31/2017 101205 DH-1 10000-S1 119 9/30/2017 9/26/2017
8/31/2017 8/31/2017 101206 DH-1 20000-S1 119 9/30/2017 10/28/2017
8/31/2017 8/31/2017 101207 AS-1 30000-S1 139 9/30/2017 9/26/2017
8/31/2017 8/31/2017 101208 AS-1 40000-S1 139 9/30/2017 1/26/2018
8/31/2017 8/31/2017 101209 AS-1 50000-S1 139 9/30/2017 12/16/2017
8/31/2017 8/31/2017 101210 AS-1 60000-S1 139 9/30/2017 1/14/2018
8/31/2017 8/31/2017 101211 DH-1 70000-C1 119 9/30/2017 10/5/2017
8/31/2017 8/31/2017 101219 DH-1 70000-C2 119 9/30/2017 10/5/2017
8/31/2017 8/31/2017 101220 DH-1 70000-C3 119 9/30/2017 10/5/2017
8/31/2017 8/31/2017 101221 DH-1 70000-C4 119 9/30/2017 10/5/2017
8/31/2017 8/31/2017 101222 DH-1 70000-C5 119 9/30/2017 10/5/2017
8/31/2017 8/31/2017 101227 AS-1 80000-C1 139 9/30/2017 10/5/2017
8/31/2017 8/31/2017 101237 AS-1 80000-C2 139 9/30/2017 10/5/2017
8/31/2017 8/31/2017 101238 AS-1 80000-C3 139 9/30/2017 10/5/2017
8/31/2017 8/31/2017 101239 AS-1 80000-C4 139 9/30/2017 10/5/2017
8/31/2017 8/31/2017 101240 AS-1 80000-C5 139 9/30/2017 10/5/2017
8/31/2017 8/31/2017 101245 AS-1 90000-S1 139 9/30/2017 11/29/2017
9/30/2017 9/30/2017 101377 DH-1 10000-S1 119 10/30/2017 10/26/2017
9/30/2017 9/30/2017 101378 DH-1 20000-S1 119 10/30/2017 10/31/2017
9/30/2017 9/30/2017 101379 AS-1 30000-S1 139 10/30/2017 10/26/2017
9/30/2017 9/30/2017 101380 AS-1 40000-S1 139 10/30/2017 2/1/2018
9/30/2017 9/30/2017 101381 AS-1 50000-S1 139 10/30/2017 3/2/2018
9/30/2017 9/30/2017 101382 AS-1 60000-S1 139 10/30/2017 2/20/2018
9/30/2017 9/30/2017 101383 DH-1 70000-C1 119 10/30/2017 11/4/2017
9/30/2017 9/30/2017 101391 DH-1 70000-C2 119 10/30/2017 11/4/2017
9/30/2017 9/30/2017 101392 DH-1 70000-C3 119 10/30/2017 11/4/2017
9/30/2017 9/30/2017 101393 DH-1 70000-C4 119 10/30/2017 11/4/2017
9/30/2017 9/30/2017 101394 DH-1 70000-C5 119 10/30/2017 11/4/2017
9/30/2017 9/30/2017 101395 DH-1 70000-C6 119 10/30/2017 11/4/2017
9/30/2017 9/30/2017 101396 DH-1 70000-C7 119 10/30/2017 11/4/2017
9/30/2017 9/30/2017 101397 DH-1 70000-C8 119 10/30/2017 11/4/2017
9/30/2017 9/30/2017 101399 AS-1 80000-C1 139 10/30/2017 11/4/2017
9/30/2017 9/30/2017 101409 AS-1 80000-C2 139 10/30/2017 11/4/2017
9/30/2017 9/30/2017 101410 AS-1 80000-C3 139 10/30/2017 11/4/2017
9/30/2017 9/30/2017 101411 AS-1 80000-C4 139 10/30/2017 11/4/2017
9/30/2017 9/30/2017 101412 AS-1 80000-C5 139 10/30/2017 11/4/2017
9/30/2017 9/30/2017 101417 AS-1 90000-S1 139 10/30/2017 1/19/2018
10/31/2017 10/31/2017 101549 DH-1 10000-S1 119 11/30/2017 11/26/2017
10/31/2017 10/31/2017 101550 DH-1 20000-S1 119 11/30/2017 1/14/2018
10/31/2017 10/31/2017 101551 AS-1 30000-S1 139 11/30/2017 11/26/2017
10/31/2017 10/31/2017 101552 AS-1 40000-S1 139 11/30/2017 2/7/2018
10/31/2017 10/31/2017 101553 AS-1 50000-S1 139 11/30/2017 12/5/2017
10/31/2017 10/31/2017 101554 AS-1 60000-S1 139 11/30/2017 3/11/2018
10/31/2017 10/31/2017 101555 DH-1 70000-C1 119 11/30/2017 12/5/2017
10/31/2017 10/31/2017 101563 DH-1 70000-C2 119 11/30/2017 12/5/2017
10/31/2017 10/31/2017 101564 DH-1 70000-C3 119 11/30/2017 12/5/2017
10/31/2017 10/31/2017 101565 DH-1 70000-C4 119 11/30/2017 12/5/2017
10/31/2017 10/31/2017 101566 DH-1 70000-C5 119 11/30/2017 12/5/2017
10/31/2017 10/31/2017 101567 DH-1 70000-C6 119 11/30/2017 12/5/2017
10/31/2017 10/31/2017 101568 DH-1 70000-C7 119 11/30/2017 12/5/2017
10/31/2017 10/31/2017 101569 DH-1 70000-C8 119 11/30/2017 12/5/2017
10/31/2017 10/31/2017 101571 AS-1 80000-C1 139 11/30/2017 12/5/2017
10/31/2017 10/31/2017 101581 AS-1 80000-C2 139 11/30/2017 12/5/2017
10/31/2017 10/31/2017 101582 AS-1 80000-C3 139 11/30/2017 12/5/2017
10/31/2017 10/31/2017 101583 AS-1 80000-C4 139 11/30/2017 12/5/2017
10/31/2017 10/31/2017 101584 AS-1 80000-C5 139 11/30/2017 12/5/2017
10/31/2017 10/31/2017 101589 AS-1 90000-S1 139 11/30/2017 12/1/2017
11/30/2017 11/30/2017 101721 DH-1 10000-S1 119 12/30/2017 12/26/2017
11/30/2017 11/30/2017 101722 DH-1 20000-S1 119 12/30/2017 3/23/2018
11/30/2017 11/30/2017 101723 AS-1 30000-S1 139 12/30/2017 12/26/2017
11/30/2017 11/30/2017 101724 AS-1 40000-S1 139 12/30/2017 1/14/2018
11/30/2017 11/30/2017 101725 AS-1 50000-S1 139 12/30/2017 1/29/2018
11/30/2017 11/30/2017 101726 AS-1 60000-S1 139 12/30/2017 2/22/2018
11/30/2017 11/30/2017 101727 DH-1 70000-C1 119 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101735 DH-1 70000-C2 119 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101736 DH-1 70000-C3 119 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101737 DH-1 70000-C4 119 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101738 DH-1 70000-C5 119 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101739 DH-1 70000-C6 119 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101740 DH-1 70000-C7 119 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101741 DH-1 70000-C8 119 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101743 AS-1 80000-C1 139 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101744 AS-1 80000-C10 139 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101753 AS-1 80000-C2 139 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101754 AS-1 80000-C3 139 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101755 AS-1 80000-C4 139 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101756 AS-1 80000-C5 139 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101757 AS-1 80000-C6 139 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101758 AS-1 80000-C7 139 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101759 AS-1 80000-C8 139 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101760 AS-1 80000-C9 139 12/30/2017 1/4/2018
11/30/2017 11/30/2017 101761 AS-1 90000-S1 139 12/30/2017 4/17/2018
12/31/2017 12/31/2017 101934 DH-1 100000-S1 119 1/30/2018 1/30/2018
12/31/2017 12/31/2017 101893 DH-1 10000-S1 119 1/30/2018 1/26/2018
12/31/2017 12/31/2017 101894 DH-1 20000-S1 119 1/30/2018 2/23/2018
12/31/2017 12/31/2017 101895 AS-1 30000-S1 139 1/30/2018 1/26/2018
12/31/2017 12/31/2017 101896 AS-1 40000-S1 139 1/30/2018 5/14/2018
12/31/2017 12/31/2017 101897 AS-1 50000-S1 139 1/30/2018 3/7/2018
12/31/2017 12/31/2017 101898 AS-1 60000-S1 139 1/30/2018 4/10/2018
12/31/2017 12/31/2017 101899 DH-1 70000-C1 119 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101907 DH-1 70000-C2 119 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101908 DH-1 70000-C3 119 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101909 DH-1 70000-C4 119 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101910 DH-1 70000-C5 119 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101911 DH-1 70000-C6 119 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101912 DH-1 70000-C7 119 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101913 DH-1 70000-C8 119 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101915 AS-1 80000-C1 139 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101916 AS-1 80000-C10 139 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101925 AS-1 80000-C2 139 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101926 AS-1 80000-C3 139 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101927 AS-1 80000-C4 139 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101928 AS-1 80000-C5 139 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101929 AS-1 80000-C6 139 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101930 AS-1 80000-C7 139 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101931 AS-1 80000-C8 139 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101932 AS-1 80000-C9 139 1/30/2018 2/4/2018
12/31/2017 12/31/2017 101933 AS-1 90000-S1 139 1/30/2018 4/20/2018

v-yalanwu-msft · ‎12-15-2021

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.

How to get your questions answered quickly -- How to provide sample data

View solution in original post

v-yalanwu-msft · ‎12-15-2021

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.

How to get your questions answered quickly -- How to provide sample data

tdixon61 · ‎12-16-2021

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

Accounts Receivable Aging based on Transaction Report

Helpful resources

Power BI Monthly Update - August 2025

Fabric Community Update - August 2025

How to Get Your Question Answered Quickly

Join us at FabCon Vienna from September 15-18, 2025

Accounts Receivable Aging based on Transaction Report

Helpful resources

Power BI Monthly Update - August 2025

Fabric Community Update - August 2025

How to Get Your Question Answered Quickly