how to calculate overdue days

[Anonymous]

 Hello Everyone,

I want to calculate overdue days, I have fact table which has posting date, i have dates table but i don't want to create calculated column in dates table, i want to use summarize table function, can you please guide me step by step.

Thank you

[amitchandak]

@Anonymous , see the approach in this blog can help, the other table is date

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

[Anonymous]

Dear Amit sir,

i have gone through your blog, it helped to understand but as per my scenario , i have to calculate date diff between current date and due date.. to calculate overdue

[ryan_mayu]

@Anonymous 

Please share the sample data and the expected results.

[Anonymous]

I have shared 2 pictures here,

so first i want to explain to you that i have customer table (customer details), second Customer Ledger Entry and Detailed Cust ledger entry,

Cust ledger entry (due date, invoice no), detailed cust ledger entry (outstanding amount, Posting date field, invoice no.),

and in the second pic i want reuslt like this..

Help me, I don't understand..

[v-shex-msft]

HI @Anonymous,

I can't access the share link it still requests some authorization, can you please fix this? (notice: do not attach sensitive data in your shared sample file, please use same structure dummy data table to instead)

For your requirement, I'd like to suggest you create two variable tables with summaries function on the 'detail' tables. 

detail custom table: Customer id, customer name(lookup from header table), invoice no, post date.

detailed cust ledger entry: invoice no, outstanding amount, due date(lookup from header table), post date.

After these, you can use addcolumns function on the variable 'customer table' to add fields from variable 'cust ledger table' based on current invoice no, post date.
Finally, you can use summarize function on the above table to merge fields and calculate the overdue days.

Regards,
Xiaoxin Sheng

[Anonymous]

Hello Sir,

Customer Ledger Entry

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

Detailed Customer Ledger Entry

Posting Date	Invoice No	Amount	Customer Code
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
04/01/2015	CR000002	-334.82	TRD00791
04/01/2015	CR000002	-334.82	TRD00791
04/01/2015	CR000002	334.82	TRD00791
04/01/2015	IN000003	354.5	TRD01349
04/01/2015	IN000004	309.6	TRD00564
04/01/2015	IN000005	3447.07	TRD00690
04/01/2015	CR000003	-455.71	TRD00690
04/01/2015	CR000003	-455.71	TRD00690
04/01/2015	CR000003	455.71	TRD00690
04/01/2015	IN000006	4531.34	TRD01379
04/01/2015	CR000004	-577.95	TRD01379
04/01/2015	CR000004	-577.95	TRD01379

Customer Tables

Customer Code	Customer Name
TRD00250	ABC Customer 01
TRD00251	BCD Customer 02
TRD00252	DCA Customer 03
TRD00253	ACD Customer 04
TRD00261	ADB Customer 05
TRD00265	CDA Customer 06
TRD00267	BDA Custoomer 07
TRD00269	ADB Customer 08
TRD00270	DBC Customer 09
TRD00276	CDA Customer 10

Expected Result...

Kindly check that how can i create two variable tables and give the connection and calculate overdues days

[v-shex-msft]

HI @Anonymous,

You can try to use following calculate table formula generated a summary table based on raw table records:

Table =
GROUPBY (
    SELECTCOLUMNS (
        ADDCOLUMNS (
            'Detailed Customer Ledger Entry',
            "DueDate", LOOKUPVALUE (
                'Customer Ledger Entry'[Due Date],
                'Customer Ledger Entry'[Customer Code], [Customer Code],
                'Customer Ledger Entry'[Invoice No], [Invoice No],
                'Customer Ledger Entry'[Invoice Date], [Posting Date]
            ),
            "Customer Name", LOOKUPVALUE (
                'Customer'[Customer Name],
                'Customer'[Customer Code], [Customer Code]
            )
        ),
        "Customer Code & Name", [Customer Code] & "/" & [Customer Name],
        "Invoice Date / Posting Date", [Posting Date],
        "Amt", [Amount],
        "Diff", DATEDIFF ( [Posting Date], [DueDate], DAY )
    ),
    [Customer Code & Name],
    [Invoice Date / Posting Date],
    "OutStanding Amt", SUMX ( CURRENTGROUP (), [Amt] ),
    "OverDue", SUMX ( CURRENTGROUP (), [Diff] )
)

Notice: current power bi not able to create dynamic calculated table/column based on filter/slicer.

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous Over due days is based on today or selected date in slicer ?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

https://drive.google.com/file/d/1S23BViH_7VWUtVwhfob04poEqeHO51tO/view?usp=sharing

Please check the link and there is data sir, i want to calculate data and expected result, i going to show in the format below..