AR Ageing Grouping

[Anonymous]

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

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

[v-yingjl]

Hi @Anonymous ,

Your expected output seems clear but there are some problems that not certain based on your description:

1. 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'
2. How did you create a group table in query editor?
3. 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