Forum Discussion
AR Ageing Grouping
Dear Everyone..
I am making AR Ageing Report & i have three tables.. one is customer details and another customer ledger entyr and third one is Detailed Customer Ledger Entry Table...
Customer Table
| Customer Code | Customer Name |
| Csut 01 | XYZ 01 |
| Cust 02 | XYZ 02 |
| Cust 03 | XYZ 03 |
| Cust 04 | XYZ 04 |
| Cust 05 | XYZ 05 |
| Cust 06 | XYZ 06 |
| Cust 07 | XYZ 07 |
| Cust 08 | XYZ 08 |
| Cust 09 | XYZ 09 |
| Cust 10 | XYZ 10 |
| Cust 11 | XYZ11 |
| Cust 12 | XYZ 12 |
| Cust 13 | XYZ 13 |
| Cust 14 | XYZ 14 |
| Cust 15 | XYZ 15 |
| Cust 16 | XYZ 16 |
| Cust 17 | XYZ 17 |
| Cust 18 | XYZ 18 |
| Cust 19 | XYZ 19 |
| Cust 20 | XYZ 20 |
| Cust 21 | XYZ 21 |
| Cust 22 | XYZ 22 |
Customer Ledger Entry Table
| Customer Code | Posting Date | Invoice No | Due Date |
| Csut 01 | 31/12/2019 | IN000001 | 31/12/2019 |
| Cust 02 | 31/12/2019 | CR000001 | 31/12/2019 |
| Cust 03 | 31/12/2019 | IN000002 | 31/12/2019 |
| Cust 04 | 31/12/2019 | CR000002 | 31/12/2019 |
| Cust 05 | 31/12/2019 | IN000003 | 31/12/2019 |
| Cust 06 | 31/12/2019 | IN000004 | 31/12/2019 |
| Cust 07 | 31/12/2019 | IN000005 | 31/12/2019 |
| Cust 08 | 31/12/2019 | CR000003 | 31/12/2019 |
| Cust 09 | 31/12/2019 | IN000006 | 31/12/2019 |
| Cust 10 | 31/12/2019 | CR000004 | 31/12/2019 |
| Cust 11 | 31/12/2019 | IN000007 | 31/12/2019 |
| Cust 12 | 31/12/2019 | IN000008 | 31/12/2019 |
| Cust 13 | 31/12/2019 | CR000005 | 31/12/2019 |
| Cust 14 | 31/12/2019 | IN000009 | 31/12/2019 |
| Cust 15 | 31/12/2019 | IN000010 | 30/03/2020 |
| Cust 16 | 31/12/2019 | IN000011 | 31/12/2019 |
| Cust 17 | 31/12/2019 | CR000006 | 31/12/2019 |
| Cust 18 | 31/12/2019 | IN000012 | 31/12/2019 |
| Cust 19 | 31/12/2019 | CR000007 | 31/12/2019 |
| Cust 20 | 31/12/2019 | IN000013 | 31/12/2019 |
| Cust 21 | 31/12/2019 | CR000008 | 31/12/2019 |
| Cust 22 | 31/12/2019 | IN000015 | 31/12/2019 |
| Cust 23 | 31/12/2019 | CR000009 | 31/12/2019 |
Detailed Customer Ledger Entry table
| Posting Date | Invoice No | Outstanding Amt | Customer Code |
| 31/12/2019 | IN000001 | 498.5 | Csut 01 |
| 31/12/2019 | CR000001 | -80.5 | Cust 02 |
| 31/12/2019 | CR000001 | -80.5 | Cust 03 |
| 31/12/2019 | CR000001 | 80.5 | Cust 04 |
| 31/12/2019 | IN000002 | 2313.46 | Cust 05 |
| 31/12/2019 | CR000002 | -334.82 | Cust 06 |
| 31/12/2019 | CR000002 | -334.82 | Cust 07 |
| 31/12/2019 | CR000002 | 334.82 | Cust 08 |
| 31/12/2019 | IN000003 | 354.5 | Cust 09 |
| 31/12/2019 | IN000004 | 309.6 | Cust 10 |
| 31/12/2019 | IN000005 | 3447.07 | Cust 11 |
| 31/12/2019 | CR000003 | -455.71 | Cust 12 |
| 31/12/2019 | CR000003 | -455.71 | Cust 13 |
| 31/12/2019 | CR000003 | 455.71 | Cust 14 |
| 31/12/2019 | IN000006 | 4531.34 | Cust 15 |
| 31/12/2019 | CR000004 | -577.95 | Cust 16 |
| 31/12/2019 | CR000004 | -577.95 | Cust 17 |
| 31/12/2019 | CR000004 | 577.95 | Cust 18 |
| 31/12/2019 | IN000007 | 207.77 | Cust 19 |
| 31/12/2019 | IN000008 | 7102.73 | Cust 20 |
| 31/12/2019 | CR000005 | -397.56 | Cust 21 |
| 31/12/2019 | CR000005 | -397.56 | Cust 22 |
| 31/12/2019 | CR000005 | 397.56 | Cust 23 |
Now i have to calculate two things first is the days left for that i have tried this measure
Days Left = VAR A = ADDCOLUMNS(SUMMARIZE('Detailed Customer Ledger Entry','Detailed Customer Ledger Entry'[Customer No_],'Detailed Customer Ledger Entry'[Amount],'Customer Ledger Entry'[Posting Date],'Customer Ledger Entry'[Due Date]), "Overdue Days",DATEDIFF('Customer Ledger Entry'[Due Date],TODAY(),DAY))
VAR B = MAXX(A,[Overdue Days])
Return
B
then for i made a table by grouping in query editor
Age GroupShort OrderMinMax
Thanks
| 0-30 Days | 1 | 0 | 30 |
| 31-60 Days | 2 | 30 | 60 |
| 61-90 Days | 3 | 60 | 90 |
| 91-120 Days | 4 | 90 | 120 |
| 121-150 Days | 5 | 120 | 150 |
| 151-180 Days | 6 | 150 | 180 |
| 181-210 Days | 7 | 180 | 210 |
| 211-240 Days | 8 | 210 | 240 |
| 241-270 Days | 9 | 240 | 270 |
| 271-300 Days | 10 | 270 | 300 |
| 301-330 Days | 11 | 300 | 330 |
| 331-365 Days | 12 | 330 | 365 |
| 365+ Days | 13 | 365 | 3650 |
after that i calculate the age wise calculation & for that i used this measure
Receivable Per Group =
CALCULATE( [Outstanding Amt],
FILTER( 'Detailed Customer Ledger Entry',
COUNTROWS(
FILTER( 'Customer Age Group',
[Days Left] >= 'Customer Age Group'[Min] &&
[Days Left] <= 'Customer Age Group'[Max] ) ) > 0 ) )
but now the problem is that both amounts are different...
outstanding amount is different and receivable amount is different...while outstanding amount is correct
outstanding amount is different and receivable amount is different...while outstanding amount is correct
Coz i simpley Calculated it by this measure
Outstanding Amt = CALCULATE(SUM('Detailed Customer Ledger Entry'[Amount]))
Can anyone Help please... is there somthing wrong my measures or do i have to calculate it by anyother ways....
KIndly Help.
Thanks
Arif
1 Reply
Hi Anonymous ,
Your expected output seems clear but there are some problems that not certain based on your description:
- In your sample table and measure formula, seems like the column name is not correspoding, for example, where is the [Amount] column if you created a measure name 'Outstanding Amt'
- How did you create a group table in query editor?
- In your same table, the due date is basic the same, but in your picture there seems different
Could you please consider share a dummy .pbix file for further discussion? Please remember to replace the sensitive message in your file.
Best Regards,
Yingjie Li
