Collection adjustment against Receivables Ageing

[Riteshheda]

Hi,

I have to write one DAX for creating a table/measure identifying collection against which Receivables Ageing. Below is the example:

Customer	Receivables Not Due	Receivables 1-30 days	Receivables 31-90 days	Receivables 91-180 days	Receivables Abv 180 days	Total Receivables	Collection Received	Collection from Not Due	Collection from 1-30 days	Collection from 31-90 days	Collection from 91-180 days	Collection from Abv 180 days	Total Collection	Advance
A	20	30	10	0	0	60	100	20	30	10			60	40
B	0	10	20	30	60	120	90				30	60	90	0
C	10	90				100	80	0	80				80	0
D	120					120	110	110					110	0
Total	150	130	30	30	60	400	380	130	110	10	30	60	340	40

Collection Received is adjusted on FIFO basis to the Receivables, i.e. oldest Receivables Bucket first, and if any balance left, then to the lower Bucket, and so on. 

If any Collection Amount remains unajusted agasint Total Receivables, then, that balance amount to be shown as Advances.

I am ablt to break the Collection amount into Not Due and OverDue, but getting mixed-up when number of buckets are increasing.

Thank you in Advance. Please help and suggest solution.

Ritesh

[Sahir_Maharaj]

Hello @Riteshheda,

1. Create a measure for each receivables aging bucket to calculate the total collection received for that specific bucket. (Collection from Not Due, etc)

2. Create a measure to calculate the total collection received for all buckets.

Total Collection = [Collection from Not Due] + [Collection from 1-30 days] + [Collection from 31-90 days] + [Collection from 91-180 days] + [Collection from Abv 180 days]

3. Create a measure to calculate the total receivables amount for all buckets.

Total Receivables = [Receivables Not Due] + [Receivables 1-30 days] + [Receivables 31-90 days] + [Receivables 91-180 days] + [Receivables Abv 180 days]

4. Create a measure to calculate the advances amount, which represents any remaining balance between the total collection received and total receivables.

Advances = [Total Receivables] - [Total Collection]

By using these measures, you can calculate the total collection received, total receivables, and advances.

Do not hesitate to let me know if you might need further assistance.

[Riteshheda]

Hello Sahir,

Thank you for your response.

My Query is how to get "Collection From Not Due", Collection from "1-30 days", Collection from "31-90 days", etc.

I only know Collection received and Receivables Ageing of Customer. 

I need to adjust Collections against oldest Receivables first (FIFO basis), if any Collection remain unadjusted in higher bucket of receivables, then it is adjusted with the earliest or 2nd highest bucket of receivables, till the time Collection is fully adjusted.

Or if Receivables got completed adjsuted and still Collection remains, then it will be considered as Advances.

Query - How to adjust Collection against different ageing of Receivables and identify Collection ageing bucket.

Last 7 columns in the first query is about desired result which I am looking to achieve through DAX.

Thank you in advance.

Ritesh