Accounts Receivable - Historical Aging $$ Trending

[sjrrkb123]

So I have perused the power bi forums and have not found a satisfactory answer on how to create a historical Accounts Receivable Aging Trend in Power BI. I saw this link and followed the links to Power Pivot info but did not really find what I was looking for.

Here is the following data I have:

• Invoice Number
• Invoice Date
• Invoice Due Date
• Invoiced Amount
• Cash Receipt Number
• Date of Cash Receipt
• Amount on Cash Receipt

Here is what my data is currently structured as:

Invoice #

Invoice Date

Invoiced Amount

Due Date

Cash Receipt

Cash Received

Date Cash Received

ABC123

10/3/2011

$           14,547

12/2/2011

PYT101

$         4,370

3/12/2012

ABC123

10/3/2011

$           14,547

12/2/2011

PYT102

$         2,264

3/12/2012

ABC123

10/3/2011

$           14,547

12/2/2011

PYT103

$         7,913

3/12/2012

ABC456

10/9/2011

$         179,451

11/18/2011

PYT104

$       76,000

10/19/2011

ABC456

10/9/2011

$         179,451

11/18/2011

PYT105

$       18,680

10/29/2011

ABC456

10/9/2011

$         179,451

11/18/2011

PYT106

$         1,912

11/10/2011

ABC456

10/9/2011

$         179,451

11/18/2011

PYT107

$       82,859

3/15/2012

Here is what I think the data should be structured like in Power BI to facilitate this analysis:

Invoice		Date Column		Balance		Days Past Due
ABC123		10/3/2011		$14,457		0
ABC123		3/12/2012		$0		101
ABC456		10/9/2011		$179,451		0
ABC456		10/19/2011		$103,451		-30
ABC456		10/29/2011		$84,771		-20
ABC456		11/10/2011		$82,859		-8
ABC456		3/15/2012		$0		118

Where the first entry is the invoiced amount for a given invoice number. Each subsequent line with that invoice number represents the remaining balance on that invoice after the cash is applied. The last line for a given invoice "should" net to zero (some clients don't always pay but in this simple data set all clients have paid their balance off).  In addition, the Days Past Due is a date difference between the due date and the date cash received. 

Note, sorry for the extra columns, every time I posted it without the extra columns the matrix looked horrible.

What I am looking for is how to structure data in this desired format as I am having a lot of trouble with it. 

My next post in this will have the sample pbix file.

@Greg_Deckler  I have tried to follow your post on how to "properly" post on this forum. TY for the info 🙂

ps...I am very open to using Power Query as well. It need not have a DAX solution.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-eqin-msft]

Hi @sjrrkb123 ,

Please try this:

Table =
VAR _t1 =
    DISTINCT (
        SELECTCOLUMNS (
            'Current State',
            "Invoice", [Invoice #],
            "Date Column", [Invoice Date]
        )
    )
VAR _t2 =
    DISTINCT (
        SELECTCOLUMNS (
            'Current State',
            "Invoice", [Invoice #],
            "Date Column", [Date Cash Received]
        )
    )
RETURN
    ADDCOLUMNS (
        UNION ( _t1, _t2 ),
        "Balance",
            VAR _first =
                LOOKUPVALUE ( 'Current State'[Invoiced Amount], [Invoice #], [Invoice] )
            RETURN
                _first
                    - CALCULATE (
                        SUM ( 'Current State'[Cash Received] ),
                        FILTER (
                            'Current State',
                            'Current State'[Date Cash Received] <= [Date Column]
                                && 'Current State'[Invoice #] = EARLIER ( [Invoice] )
                        )
                    )
    )

Then use the following formula to add a column:

Days Past Due =
VAR _diff =
    DATEDIFF (
        LOOKUPVALUE ( 'Current State'[Due Date], [Invoice #], [Invoice] ),
        [Date Column],
        DAY
    )
RETURN
    IF (
        [Date Column]
            = MINX (
                FILTER ( 'Table', 'Table'[Invoice] = EARLIER ( 'Table'[Invoice] ) ),
                [Date Column]
            ),
        0,
        _diff
    )

The final output is shown below:

Please kindly take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sjrrkb123]

Here is a sample pbix file with the dummy data included. 
https://drive.google.com/file/d/1NdUKvMqbU2yGZuMnbBaq-b55izHfkg19/view?usp=sharing