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
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
@Anonymous , see the approach in this blog can help, the other table is date
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
@Anonymous
Please share the sample data and the expected results.
Proud to be a Super User!
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..
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
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
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
@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.
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..
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |