Historical Account Receivables

[abelrmg]

Hi All,

I want some ideas for my report. The scope is make a month balance for ammount by status for the not payed invoices.

I want it to be dynamic and to be updated by the date chosen in slicer.

The data is this:

Invoice ID	Trans date DD/MM/YYYY	Due date DD/MM/YYYY	Payment Date DD/MM/YYYY	Ammount
001	01/12/2019	25/12/2029	26/12/2019	$  700.00
002	15/12/2019	30/12/2019	15/01/2020	$  650.00
003	24/12/2019	24/01/2020	01/02/2020	$  550.00
004	01/01/2020	16/01/2020	21/01/2020	$3,000.00
005	03/01/2020	02/02/2020	07/02/2020	$2,000.00
006	04/01/2020	19/01/2020	20/01/2020	$1,500.00
007	05/01/2020	25/01/2020	01/03/2020	$  800.00
008	01/02/2020	02/03/2020	02/03/2020	$3,500.00
009	03/03/2020	08/03/2020	13/03/2020	$1,200.00

The result i hope is:

If the filter is the date 31/12/2019 (DD/MM/YYYY), the report must first consider only the invoices that have been made up to that date, then consider only the invoices that have not been paid, and assign the status according to the due date.

Result example:

Dec-19
Ammount	Status
$   650.00	Due
$  550.00	On time

Invoices002,003 

If the filter is 31/01/2020 (DD/MM/YYYY), the balance of December 2019 is the same, and then apply the las rules I mentioned.

Result example:

Invoices: for January 003,007 y 005

The Result for 29/02/2020 ((DD/MM/YYYY)

The invoices= for February 007 and 008

The challenge is that an invoice can appear in several months with different statuses

Regards

[Syndicate_Admin]

Good day, dear, I ask for help with regard to the control of accounts receivable. I have two tables: the first is a table of accounts receivable and the second is a table of credits made by customers. What I need is a historical balance of accounts receivable, that is if I select a date that shows me all the active accounts receivable with their balance and in a column that appears the sum of all the credits made by the customer that are within the selected date. the tables to use are as follows

[v-diye-msft · Accepted solution]

Hi @abelrmg 

Sorry for my late reply, it seems I figured it out:

Add a calendar table as a slicer, then add the measure:

Measure = var a = SELECTEDVALUE('Table 2'[Date])
Return
IF(MAX('Table'[Payment Date])<=a,BLANK(),IF(MAX('Table'[Payment Date])>a&&MAX('Table'[Due date])<=a,"Due",IF(MAX('Table'[Due date])>a&&MAX('Table'[Trans date])<=a,"Ontime")))

Pbix attached.

[abelrmg]

@v-diye-msft , thank you for your advice.

Regards,

[v-diye-msft]

Hi @abelrmg 

Sorry I don't quite understand your logic, how did you get the amount 50? also, if the filter date is 29/12/2019, why invoice id 003 could be filtered?

Dec-19
Amount	Status
$    50.00	Due
$  550.00	On time

Please kindly elaborate more.

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[abelrmg]

Hello @v-diye-msft , the amount 50 was incorrect, the correct amount is 650 for the invoice 002, I already edited the post, than you for your correction.

The invoice 003 should be filtered because the trans date is before for the report date filtered and at the time of the query this invoice is an account receivable.

I will try to do in a PBI file, thanks.

Regards,

Abel

[v-diye-msft]

Hi @abelrmg 

Sorry for my late reply. I'm, still confused about:

1. If the filter date > Due date, then the invoice status should be Due (No matter with the payment date, right?)

2. If the due date for 001 is 25/12/2019? then it should be due as well if the filter date is 31/12/2019, right?

Invoice ID	Trans date DD/MM/YYYY	Due date DD/MM/YYYY	Payment Date DD/MM/YYYY	Amount
001	01/12/2019	25/12/2029(should be 25/12/2019?)	26/12/2019	$  700.00
002	15/12/2019	30/12/2019	15/01/2020	$  650.00
003	24/12/2019	24/01/2020	01/02/2020	$  550.00
004	01/01/2020	16/01/2020	21/01/2020	$3,000.00
005	03/01/2020	02/02/2020	07/02/2020	$2,000.00
006	04/01/2020	19/01/2020	20/01/2020	$1,500.00
007	05/01/2020	25/01/2020	01/03/2020	$  800.00
008	01/02/2020	02/03/2020	02/03/2020	$3,500.00
009	03/03/2020	08/03/2020	13/03/2020	$1,200.00

[abelrmg]

Hi @v-diye-msft , in other words the objetive of the report is accumulate the amount of the invoices that have not been paid as of the date of the consultation in the current month and the last date of the past months.

Trans date, is when the invoice is created 

Due date, is when the customer must paid the invoice

Payment date, is when the customer pais the invoice

1. If the filter date > Due date, then the invoice status should be Due (No matter with the payment date, right?),

Yes, but the payment date if it matters for the calculation, if the payment date is < for the filter date, the invoice should not be considered because is closed.

2. If the due date for 001 is 25/12/2019? then it should be due as well if the filter date is 31/12/2019, right?

No, because is closed. Only if the payment date was after the consultation date

Thanks for your time

Regards,

[amitchandak]

I did not get all your issues. But you can Due like

Due=
var _max =maxx(Date, Date[Date])
return
calculate(sum(table[Amount]),filter(table, table[Due date]<=_max && table[payment date]>=_max))

Or

Due=
var _max =maxx(Date, Date[Date])
return
calculate(sum(table[Amount]),filter(table, table[Due date]<=max(Date[Date]) && table[payment date]>=max(Date[Date])))