Forum Discussion
Collection adjustment against Receivables Ageing
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.
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
