Forum Discussion
Historical Account Receivables
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
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.
8 Replies
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])))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.
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
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/YYYYDue 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
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.
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
