How to show a list of invoices a payment being allocated to in Power BI

[rs1249]

Hi everyone, we are using s system called dynamics business central,

I need to run a report to show a list of invoices a payment is allocated to in power bi report, but just couldn’t figure out how our system manage to do it, and replicate that logic in power bi.

So I have two tables  "Cust_ledgerEntries" and "Detailed_Cust_Ledg_Entries", the relatetionship between the two tables is: "Cust_ledgerEntries"[Entr_No]--1:Many-->"Detailed_Cust_Ledg_Entries"[Cust_Ledger_Entry_No]

I can see in "Detailed_Cust_Ledg_Entries", When a invoice or payment posted, it creates an entry for each and set the entry type as "Initial Entry", each has "Detailed_Cust_Ledg_Entries"[Cust_Ledger_Entry_No] same as the "Cust_ledgerEntries"[Entr_No]. Something like this 

Invoice1 -200 1254

Invoice2 -100 1263

Invoice3 -200 1374

Payment 500 1568

when a payment is applied to an invoice or credit note, what system did was create two or more entries, with entry type sets as "Application" and their amounts offsets each other, Then cross assigns the [Cust_Ledger_Entry_No]

something Like below,

11 Payment -500 1568

16 Payment 200 1254

20 Payment 100 1263

31 Payment 200 1374

so that total balance remains the same. if I run power bi report  based on "Cust_ledgerEntries"[Entr_No], it does show all the payments and invoices been allcoated, but if I want to showa list of invoices being allocated to, there is no obvious link to do that?

I have looking at datasets for a week now, found no links to enable me to group the application entries together , but somehow our system can find way to do it, which drives me nuts. Can someone help me to see how should I use dax to do this?

Ideally, I would like the table to show something like this:

Document Type	Document No.	Posting Date	Document Type	Document No.	Description	Amount	Entry No.
Payment	BANK-0000928	01/07/2018	Invoice	INV-0001092	Invoice INV-0001092	1680	21541
Payment	BANK-0000928	01/07/2018	Invoice	INV-0001106	Invoice INV-0001106	84.86	21590
Payment	BANK-0000928	01/08/2018	Invoice	INV-0001267	Invoice INV-0001267	1680	23956
Payment	BANK-0000928	01/09/2018	Invoice	INV-0001415	Invoice INV-0001415	1680	25472
Payment	BANK-0000928	01/09/2018	Invoice	INV-0001416	Invoice INV-0001416	72	25475

The "Detailed_Cust_Ledg_Entries" table is like below:

Entry_No	Posting_Date	Entry_Type	Document_Type	Document_No	Customer_No	Amount	Debit_Amount	Credit_Amount	Initial_Entry_Due_Date	Source_Code	Unapplied	Unapplied_by_Entry_No	Cust_Ledger_Entry_No
4207	01/07/2018	Initial Entry	Invoice	INV-0001092	C0000050	1680	1680	0	01-Jul-18	SALES	FALSE	0	21541
4221	01/07/2018	Initial Entry	Invoice	INV-0001106	C0000050	84.86	84.86	0	01-Jul-18	SALES	FALSE	0	21590
4771	01/08/2018	Initial Entry	Invoice	INV-0001267	C0000050	1680	1680	0	01-Aug-18	SALES	FALSE	0	23956
5329	01/09/2018	Initial Entry	Invoice	INV-0001415	C0000050	1680	1680	0	01-Sep-18	SALES	FALSE	0	25472
5330	01/09/2018	Initial Entry	Invoice	INV-0001416	C0000050	72	72	0	01-Sep-18	SALES	FALSE	0	25475
5945	30/09/2018	Initial Entry	Invoice	INV-0001536	C0000050	5040	5040	0	30-Sep-18	SALES	FALSE	0	27381
5946	30/09/2018	Initial Entry	Invoice	INV-0001537	C0000050	15120	15120	0	30-Sep-18	SALES	FALSE	0	27398
7336	21/11/2018	Initial Entry	Payment	BANK-0000928	C0000050	-30240	0	30240	21-Nov-18	PAYMENTJNL	FALSE	0	32306
8781	21/11/2018	Application	Payment	BANK-0000928	C0000050	-1680	0	1680	01-Jul-18	SALESAPPL	FALSE	0	21541
8782	21/11/2018	Application	Payment	BANK-0000928	C0000050	25356.86	25356.86	0	21-Nov-18	SALESAPPL	FALSE	0	32306
8783	21/11/2018	Application	Payment	BANK-0000928	C0000050	-84.86	0	84.86	01-Jul-18	SALESAPPL	FALSE	0	21590
8784	21/11/2018	Application	Payment	BANK-0000928	C0000050	-1680	0	1680	01-Aug-18	SALESAPPL	FALSE	0	23956
8785	21/11/2018	Application	Payment	BANK-0000928	C0000050	-1680	0	1680	01-Sep-18	SALESAPPL	FALSE	0	25472
8786	21/11/2018	Application	Payment	BANK-0000928	C0000050	-72	0	72	01-Sep-18	SALESAPPL	FALSE	0	25475
8787	21/11/2018	Application	Payment	BANK-0000928	C0000050	-5040	0	5040	30-Sep-18	SALESAPPL	FALSE	0	27381
8788	21/11/2018	Application	Payment	BANK-0000928	C0000050	-15120	0	15120	30-Sep-18	SALESAPPL	FALSE	0	27398
13713	01/06/2019	Application	Payment	BANK-0000928	C0000050	381.3	381.3	0	21-Nov-18	SALESAPPL	TRUE	16773	32306
15397	01/10/2019	Initial Entry	Invoice	INV-0003860	C0000050	36	36	0	04-Oct-19	SALES	FALSE	0	77851
16003	26/10/2019	Initial Entry	Invoice	INV-0004012	C0000050	384	384	0	28-Oct-19	SALES	FALSE	0	79816
16773	01/06/2019	Application	Payment	BANK-0000928	C0000050	-381.3	-381.3	0	21-Nov-18	UNAPPSALES	TRUE	13713	32306
16933	01/12/2019	Initial Entry	Invoice	INV-0004232	C0000050	1226.1	1226.1	0	04-Dec-19	SALES	FALSE	0	84033
17938	01/12/2019	Application	Payment	BANK-0000928	C0000050	-36	0	36	04-Oct-19	SALESAPPL	FALSE	0	77851
17939	01/12/2019	Application	Payment	BANK-0000928	C0000050	1646.1	1646.1	0	21-Nov-18	SALESAPPL	FALSE	0	32306
17940	01/12/2019	Application	Payment	BANK-0000928	C0000050	-384	0	384	28-Oct-19	SALESAPPL	FALSE	0	79816
17941	01/12/2019	Application	Payment	BANK-0000928	C0000050	-1226.1	0	1226.1	04-Dec-19	SALESAPPL	FALSE	0	84033
19697	01/01/2020	Initial Entry	Invoice	INV-0004904	C0000050	444.06	444.06	0	04-Jan-20	SALES	FALSE	0	95060
20266	01/01/2020	Application	Invoice	INV-0004904	C0000050	444.06	444.06	0	21-Nov-18	SALESAPPL	FALSE	0	32306
20267	01/01/2020	Application	Invoice	INV-0004904	C0000050	-444.06	0	444.06	04-Jan-20	SALESAPPL	FALSE	0	95060
20649	01/02/2020	Initial Entry	Invoice	INV-0005238	C0000050	14.22	14.22	0	04-Feb-20	SALES	FALSE	0	97627
20705	01/02/2020	Application	Payment	BANK-0000928	C0000050	278.22	278.22	0	21-Nov-18	SALESAPPL	TRUE	21195	32306
20706	01/02/2020	Application	Payment	BANK-0000928	C0000050	-14.22	0	14.22	04-Feb-20	SALESAPPL	TRUE	21196	97627
21195	01/02/2020	Application	Payment	BANK-0000928	C0000050	-278.22	-278.22	0	21-Nov-18	UNAPPSALES	TRUE	20705	32306
21201	01/02/2020	Application	Invoice	INV-0005238	C0000050	14.22	14.22	0	21-Nov-18	SALESAPPL	FALSE	0	32306
21431	01/03/2020	Initial Entry	Invoice	INV-0005460	C0000050	68.4	68.4	0	04-Mar-20	SALES	FALSE	0	101417
21556	01/03/2020	Application	Invoice	INV-0005460	C0000050	68.4	68.4	0	21-Nov-18	SALESAPPL	FALSE	0	32306
21557	01/03/2020	Application	Invoice	INV-0005460	C0000050	-68.4	0	68.4	04-Mar-20	SALESAPPL	FALSE	0	101417

[Anonymous]

Hi @rs1249 ，

I have been studying your issue for quite a few hours,but I still feel confused about how to get the output as you posted.Can you provide more details to make your requirement more clear?

Much appreciated.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[rs1249]

Hi Kelly, thanks very much for look at this for me.

there are two tables,

1. customer ledger table,
2. detailed customer ledger table.

Customer ledger table recording all the customer ledger transactions such as invoices, credit notes, payments and refund. but there is no linked to show which payments is for which invoice or which refund is to which credit notse.

Detailed customer ledger table provides link for it to show which payment is for which invoice.

Requirement is to

1. find the logic behind the link
2. Following that logic to implement the logic in DAX to show  list of invoices a payment being allocated to

for Customer ledger sample data as below:

Entry_No	Customer_No	Posting_Date	Due_Date	Document_Type	Document_No	Source_Code	Amount	Remaining_Amount
19149	C0000050	01/08/2018	01/08/2018	Invoice	INV-0001041	SALES	12300	0
21541	C0000050	01/07/2018	01/07/2018	Invoice	INV-0001092	SALES	1680	0
21590	C0000050	01/07/2018	01/07/2018	Invoice	INV-0001106	SALES	84.86	0
23732	C0000050	01/08/2018	01/08/2018	Invoice	INV-0001221	SALES	12300	0
23956	C0000050	01/08/2018	01/08/2018	Invoice	INV-0001267	SALES	1680	0
25472	C0000050	01/09/2018	01/09/2018	Invoice	INV-0001415	SALES	1680	0
25475	C0000050	01/09/2018	01/09/2018	Invoice	INV-0001416	SALES	72	0
27381	C0000050	30/09/2018	30/09/2018	Invoice	INV-0001536	SALES	5040	0
27398	C0000050	30/09/2018	30/09/2018	Invoice	INV-0001537	SALES	15120	0
32306	C0000050	21/11/2018	21/11/2018	Payment	BANK-0000928	PAYMENTJNL	-30240	-2710.36
34448	C0000050	07/12/2018	07/12/2018	Payment	BANK-0000988	CASHRECJNL	-24600	0
77851	C0000050	01/10/2019	04/10/2019	Invoice	INV-0003860	SALES	36	0
79816	C0000050	26/10/2019	28/10/2019	Invoice	INV-0004012	SALES	384	0
84033	C0000050	01/12/2019	04/12/2019	Invoice	INV-0004232	SALES	1226.1	0
95060	C0000050	01/01/2020	04/01/2020	Invoice	INV-0004904	SALES	444.06	0
97627	C0000050	01/02/2020	04/02/2020	Invoice	INV-0005238	SALES	14.22	0
101417	C0000050	01/03/2020	04/03/2020	Invoice	INV-0005460	SALES	68.4	0

[rs1249]

the detailed customer ledger table as below:

Entry_No	Posting_Date	Entry_Type	Document_Type	Document_No	Customer_No	Amount	Source_Code	Unapplied	Unapplied_by_Entry_No	Cust_Ledger_Entry_No
3766	01/08/2018	Initial Entry	Invoice	INV-0001041	C0000050	12300	SALES	FALSE	0	19149
4207	01/07/2018	Initial Entry	Invoice	INV-0001092	C0000050	1680	SALES	FALSE	0	21541
4221	01/07/2018	Initial Entry	Invoice	INV-0001106	C0000050	84.86	SALES	FALSE	0	21590
4726	01/08/2018	Initial Entry	Invoice	INV-0001221	C0000050	12300	SALES	FALSE	0	23732
4771	01/08/2018	Initial Entry	Invoice	INV-0001267	C0000050	1680	SALES	FALSE	0	23956
5329	01/09/2018	Initial Entry	Invoice	INV-0001415	C0000050	1680	SALES	FALSE	0	25472
5330	01/09/2018	Initial Entry	Invoice	INV-0001416	C0000050	72	SALES	FALSE	0	25475
5945	30/09/2018	Initial Entry	Invoice	INV-0001536	C0000050	5040	SALES	FALSE	0	27381
5946	30/09/2018	Initial Entry	Invoice	INV-0001537	C0000050	15120	SALES	FALSE	0	27398
8116	07/12/2018	Initial Entry	Payment	BANK-0000988	C0000050	-24600	CASHRECJNL	FALSE	0	34448
8117	07/12/2018	Application	Payment	BANK-0000988	C0000050	-12300	CASHRECJNL	FALSE	0	19149
8118	07/12/2018	Application	Payment	BANK-0000988	C0000050	24600	CASHRECJNL	FALSE	0	34448
8119	07/12/2018	Application	Payment	BANK-0000988	C0000050	-12300	CASHRECJNL	FALSE	0	23732
8781	21/11/2018	Application	Payment	BANK-0000928	C0000050	-1680	SALESAPPL	FALSE	0	21541
8783	21/11/2018	Application	Payment	BANK-0000928	C0000050	-84.86	SALESAPPL	FALSE	0	21590
8784	21/11/2018	Application	Payment	BANK-0000928	C0000050	-1680	SALESAPPL	FALSE	0	23956
8785	21/11/2018	Application	Payment	BANK-0000928	C0000050	-1680	SALESAPPL	FALSE	0	25472
8786	21/11/2018	Application	Payment	BANK-0000928	C0000050	-72	SALESAPPL	FALSE	0	25475
8787	21/11/2018	Application	Payment	BANK-0000928	C0000050	-5040	SALESAPPL	FALSE	0	27381
8788	21/11/2018	Application	Payment	BANK-0000928	C0000050	-15120	SALESAPPL	FALSE	0	27398
15397	01/10/2019	Initial Entry	Invoice	INV-0003860	C0000050	36	SALES	FALSE	0	77851
16003	26/10/2019	Initial Entry	Invoice	INV-0004012	C0000050	384	SALES	FALSE	0	79816
16933	01/12/2019	Initial Entry	Invoice	INV-0004232	C0000050	1226.1	SALES	FALSE	0	84033
17938	01/12/2019	Application	Payment	BANK-0000928	C0000050	-36	SALESAPPL	FALSE	0	77851
17940	01/12/2019	Application	Payment	BANK-0000928	C0000050	-384	SALESAPPL	FALSE	0	79816
17941	01/12/2019	Application	Payment	BANK-0000928	C0000050	-1226.1	SALESAPPL	FALSE	0	84033
19697	01/01/2020	Initial Entry	Invoice	INV-0004904	C0000050	444.06	SALES	FALSE	0	95060
20267	01/01/2020	Application	Invoice	INV-0004904	C0000050	-444.06	SALESAPPL	FALSE	0	95060
20649	01/02/2020	Initial Entry	Invoice	INV-0005238	C0000050	14.22	SALES	FALSE	0	97627
20706	01/02/2020	Application	Payment	BANK-0000928	C0000050	-14.22	SALESAPPL	TRUE	21196	97627
21196	01/02/2020	Application	Payment	BANK-0000928	C0000050	14.22	UNAPPSALES	TRUE	20706	97627
21202	01/02/2020	Application	Invoice	INV-0005238	C0000050	-14.22	SALESAPPL	FALSE	0	97627
21431	01/03/2020	Initial Entry	Invoice	INV-0005460	C0000050	68.4	SALES	FALSE	0	101417
21557	01/03/2020	Application	Invoice	INV-0005460	C0000050	-68.4	SALESAPPL	FALSE	0	101417

[rs1249]

As you can see from above two tables:

• The Customer ledger table contains two payments and load invoices, there is no linkage between payment and invoices,
• The Detailed customer table provides detailed linkage between payments and invoices.

For example for payment 34448, it is applied to Invoice 19149 and 23732 so in the detailed customer ledger transactions it shows as follow:

Entry_No	Posting_Date	Entry_Type	Document_Type	Document_No	Customer_No	Amount	Source_Code	Unapplied	Unapplied_by_Entry_No	Cust_Ledger_Entry_No
3766	01/08/2018	Initial Entry	Invoice	INV-0001041	C0000050	12300	SALES	FALSE	0	19149
4726	01/08/2018	Initial Entry	Invoice	INV-0001221	C0000050	12300	SALES	FALSE	0	23732
8116	07/12/2018	Initial Entry	Payment	BANK-0000988	C0000050	-24600	CASHRECJNL	FALSE	0	34448
8117	07/12/2018	Application	Payment	BANK-0000988	C0000050	-12300	CASHRECJNL	FALSE	0	19149
8118	07/12/2018	Application	Payment	BANK-0000988	C0000050	24600	CASHRECJNL	FALSE	0	34448
8119	07/12/2018	Application	Payment	BANK-0000988	C0000050	-12300	CASHRECJNL	FALSE	0	23732

 as you can see when a payment is allocated to the invoices, it generates three consecutive transactions:

8117	07/12/2018	Application	Payment	BANK-0000988	C0000050	-12300	CASHRECJNL	FALSE	0	19149
8118	07/12/2018	Application	Payment	BANK-0000988	C0000050	24600	CASHRECJNL	FALSE	0	34448
8119	07/12/2018	Application	Payment	BANK-0000988	C0000050	-12300	CASHRECJNL	FALSE	0	23732

with one offsets itself and two more point at invoices,

I am wondering how I can implement dax to reflect that logic

[Anonymous]

Hi  @rs1249 ,

First create a table as below:

Table 2 = ALL('Table'[Cust_Ledger_Entry_No])

Then create a calculated column as below:

New Document_No = 
IF(SEARCH("BANK",'Table'[Document_No],1,0)>0,'Table'[Document_No],CALCULATE(MIN('Table'[Document_No]),ALLEXCEPT('Table','Table'[Cust_Ledger_Entry_No]))
 
)

Finally create 2

_Cust_Ledger_Entry_No = 
var _document=MINX(FILTER(ALL('Table'),'Table'[Cust_Ledger_Entry_No]=SELECTEDVALUE('Table 2'[Cust_Ledger_Entry_No])),'Table'[Document_No])
Return
IF(MAX('Table'[New Document_No])=_document,MAX('Table'[Cust_Ledger_Entry_No]),BLANK())

measures as below:

Measure = IF(MAX('Table'[Entry_Type])="Application"&&'Table'[_Cust_Ledger_Entry_No]<>BLANK(),MAX('Table'[Cust_Ledger_Entry_No]),BLANK())

And you will see:

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[rs1249]

Hi Kelly, thanks for solution you provided,  it only worked  for that payment, it didn't work for payment in the customer ledger with entry number 32306 though?

32306

C0000050

21/11/2018

21/11/2018

Payment

BANK-0000928

PAYMENTJNL

-30240

-2710.36

[Anonymous]

Hi  @rs1249,

The sample data you provided before doesnt contain "32306":

Could you pls update your sample data?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!