Calculating overdue days

[Anonymous]

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

[MFelix]

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.