Forum Discussion
Calculating overdue days
Hello Everyone,
I have 3 tables, i have to calculate overdues days
Customer Ledger Entry table
| Customer Code | Invoice Date | Invoice No | Due Date |
| TRD00755 | 04/01/2015 | IN000001 | 04/01/2015 |
| TRD00755 | 04/01/2015 | CR000001 | 04/01/2015 |
| TRD00791 | 04/01/2015 | IN000002 | 04/01/2015 |
| TRD00791 | 04/01/2015 | CR000002 | 04/01/2015 |
| TRD01349 | 04/01/2015 | IN000003 | 04/01/2015 |
| TRD00564 | 04/01/2015 | IN000004 | 04/01/2015 |
| TRD00690 | 04/01/2015 | IN000005 | 04/01/2015 |
| TRD00690 | 04/01/2015 | CR000003 | 04/01/2015 |
| TRD01379 | 04/01/2015 | IN000006 | 04/04/2015 |
| TRD01379 | 04/01/2015 | CR000004 | 04/01/2015 |
| TRD01167 | 04/01/2015 | IN000007 | 04/01/2015 |
| TRD00942 | 04/01/2015 | IN000008 | 04/01/2015 |
| TRD00942 | 04/01/2015 | CR000005 | 04/01/2015 |
Detailed Cust Ledger Entry
| Posting Date | Invoice No | Amount | Customer Code |
| 01/01/1753 | 0 | ||
| 04/01/2015 | IN000001 | 498.5 | TRD00755 |
| 04/01/2015 | CR000001 | -80.5 | TRD00755 |
| 04/01/2015 | CR000001 | -80.5 | TRD00755 |
| 04/01/2015 | CR000001 | 80.5 | TRD00755 |
| 04/01/2015 | IN000002 | 2313.46 | TRD00791 |
Customer table
| Customer Code | Customer Name |
| TRD00250 | Indian Association Umm Al Quwain (IAU) |
| TRD00251 | Pharmatrade(Abu Dhabi) |
| TRD00252 | United Medical Supplies (UNIMED)L.L.C |
| TRD00253 | Ambassador Pharmacy |
| TRD00261 | Al Ahliya Pharmacy |
Expected Result Format
How do i calculate overdues days..
Kindly Help
1 Reply
Hi Anonymous ,
You need to create a date table and then make the following:
- Relationships:
- DimDate -> CustomerLedgerEntry
- Customer -> Customer Ledger Entry
- CustomerLedger Entry -> Detailed Cust Ledger
- Date slicer (based on dimDate table)
- Customer slicer based on customer table)
- Now create the following measure:
Overdue Days = IF ( SUM ( 'Detailed Cust Ledger Entry'[Amount] ) = BLANK (); BLANK (); DATEDIFF ( SELECTEDVALUE ( 'Customer Ledger Entry'[Due Date] ); TODAY (); DAY ) )Check PBIX file attach, please be aware that this file has a relationship between the invoice date an the dimdate so the slicer works looking for the invoice date if you want to slice accordingly to the due date you need to make the relationship with Due date column.
- Relationships:
