Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

how to calculate overdue days

 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

9 REPLIES 9
amitchandak
Super User
Super User

@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

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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
Super User
Super User

@Anonymous 

 

Please share the sample data and the expected results.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

xyz.JPG

 

 

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..

Result.JPG

 

Help me, I don't understand..

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hello Sir,

 

 

Customer Ledger Entry

 

Customer CodeInvoice DateInvoice NoDue Date
TRD0075504/01/2015IN00000104/01/2015
TRD0075504/01/2015CR00000104/01/2015
TRD0079104/01/2015IN00000204/01/2015
TRD0079104/01/2015CR00000204/01/2015
TRD0134904/01/2015IN00000304/01/2015
TRD0056404/01/2015IN00000404/01/2015
TRD0069004/01/2015IN00000504/01/2015
TRD0069004/01/2015CR00000304/01/2015
TRD0137904/01/2015IN00000604/04/2015
TRD0137904/01/2015CR00000404/01/2015
TRD0116704/01/2015IN00000704/01/2015

 

Detailed Customer Ledger Entry

 

Posting DateInvoice NoAmountCustomer Code
04/01/2015IN000001498.5TRD00755
04/01/2015CR000001-80.5TRD00755
04/01/2015CR000001-80.5TRD00755
04/01/2015CR00000180.5TRD00755
04/01/2015IN0000022313.46TRD00791
04/01/2015CR000002-334.82TRD00791
04/01/2015CR000002-334.82TRD00791
04/01/2015CR000002334.82TRD00791
04/01/2015IN000003354.5TRD01349
04/01/2015IN000004309.6TRD00564
04/01/2015IN0000053447.07TRD00690
04/01/2015CR000003-455.71TRD00690
04/01/2015CR000003-455.71TRD00690
04/01/2015CR000003455.71TRD00690
04/01/2015IN0000064531.34TRD01379
04/01/2015CR000004-577.95TRD01379
04/01/2015CR000004-577.95TRD01379

 

 

Customer Tables

 

Customer CodeCustomer Name
TRD00250ABC Customer 01
TRD00251BCD Customer 02
TRD00252DCA Customer 03
TRD00253ACD Customer 04
TRD00261ADB Customer 05
TRD00265CDA Customer 06
TRD00267BDA Custoomer 07
TRD00269ADB Customer 08
TRD00270DBC Customer 09
TRD00276CDA Customer 10

 

Expected Result...

 

Expected Result.JPG

 

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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..Result.JPG

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.