AR Aging report by invoice with multiple pmts

[Dellis81]

Hello!

I am struggling creating an aging report x invoice that may have multiple pmts against a single invoice.  But, I'm not getting past simple math right now.   I am relatively new to this technology - appreciate your help!   I have reviewed multiple conversations in this forum, but not quite finding what I need.

I have created a shareable google drive link if anyone wants to review what I have in place (I have deleted many attempts to simplify :).   https://drive.google.com/open?id=1CoYLWkuG_M4pW8-8zCZnI3EclxQ4uly5

 Transactional data (simplifed) that is brought into PBI - with Entry Types of "R" - Receivables, and "D", deposits.   For each Deposit - the description field matches to the InvoiceID of the Receivable entry.   In hte example below, VendorA - the deposit on 1/19/19 is for invoice 2888 dated 12/26/18.

Vendor B, Invoice 67408 is an example of multiple pmts, with a balance remaining.

Date	Account	Vendor	Amount	Description	Invoice ID	EntryType
12/26/2018	1055	VendorA	5400	Deposit: 11	2888	R
1/19/2019	1055	VendorA	-5400	2888	11	D
10/17/2017	1055	VendorB	6197.31	Deposit: 40	206	R
10/23/2017	1055	VendorB	-6197.31	206	40	D
3/27/2017	1055	VendorB	8840.93	Deposit: 32	67408	R
3/27/2017	1055	VendorB	-6000	67408	11	D
6/1/2017	1055	VendorB	-2000	67408	32	D
11/26/2019	1055	VendorB	5168		3681	R

The below measure - my first attempt at least gets the amount paid for all invoices x customer - which obviously is wrong - for multiple reasons.

AcctsRecPaid = calculate([Amount1],Data[EntryType]<>"R",all(Data[Invoice ID]))

Then I tried to implement the keepfilters (or variants of the filter) - to match the InvoiceID with deposit description and that blanks everything out.

AcctsRecPaidKF = calculate([Amount1],Data[EntryType]<>"R",all(Data[Invoice ID]),filter(Data,Data[Invoice ID]=max(Data[Description])))

The desired report I want to get to is mocked up in excel (see below)

						Today	12/30/2019
Date	Account	Vendor	Invoice ID	Amount	Pmt	NetDue	OpenDays	WtAvg OpenDays	Number of Pmts	0-30	30-60	60-90	>90
12/26/2018	1055	VendorA	2888	5,400.00	(5,400.00)	0.00	24	24	1	5,400.00
10/17/2017	1055	VendorB	206	6,197.31	(6,197.31)	0.00	6	6	1	6,197.31
3/27/2017	1055	VendorB	67408	8,840.93	(8,000.00)	840.93	1,008	??	2	6,000.00		2,000.00	840.93
11/26/2019	1055	VendorB	3681	5,168.00	0.00	5,168.00	34	34	0		5,168.00

Again, thank you - both excel and pbix file should be on the shareable link provided above.

thanks

[v-xicai]

Hi @Dellis81 ,

>>The desired report I want to get to is mocked up in excel (see below).

From your desired report, could you please show us which columns or measures would you like to get and what's the logic to get them?

Best Regards,

Amy

Community Support Team _ Amy

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

[Dellis81]

Thanks for your response Amy!

First, I am struggling just getting started with this report - in getting the deposits matched with each individual AR invoice.  

My first attempt at AcctsRecPaid column is this measure

AcctsRecPaid = calculate([Amount1],Data[EntryType]<>"R",ALLEXCEPT(Data,Data[Vendor]),Data[Invoice ID])

Which at least got the proper total amount paid by vendor - but not by Invoice ID.

The below measure is my initial (& feeble) attempt at trying to get the amount paid for each invoice.

AcctsRecPaidKF = 

VAR Match0 = CALCULATETABLE(Data,all(Data),Data[Description]="206",Data[Invoice ID]="206")

VAR CalcMatch = calculate([AcctsRecPaid],Match0)
//VAR CalcMatch = calculate([AcctsRecPaid])
Return
//if(MAX(Data[EntryType])="R",CalcMatch,blank())
CalcMatch

The logic behing this measure - is to match every entry type NOT R with corresponding AR invoice.   The matching criteria is InvoiceID (type R) matches the "Description" within all deposits (type D).

So, for invoice 67408 there were two deposits (circled in blue) - and the total of those two deposits would be the value shown in "Amount Paid.

As for the remaining columns - I have not thought thru the logic much yet.     I have seen numerous examples of "aging buckets" on this forum, but havent dug into to much - not until I get this initial logic in place by invoice.

thanks for your thoughts and time!