Debtors ageing measure

[gauravnarchal]

Need help!!

I have made the ageing group table as below. I have the invoice date, payment receipt date and invoice amount per transaction under per client group.

Age1   1-30

Age2    31-60

Age3      61-90

Age4      90+

Over a selection of particular date, I need the outstanding invoice per ageing group (Clients). Can you pls help how this can be done?

[v-yiruan-msft]

Hi @gauravnarchal ,

You can create a measure as below:

Measure =
CALCULATE (
    SUM ( 'Table'[invoice amount] ),
    FILTER (
        'Table',
        'Table'[client group] = MAX ( 'Table'[client group] )
            && 'Table'[invoice date] = SELECTEDVALUE ( 'Date'[date] )
    )
)

If the above measure is not applicable for your scenario, please provide some sample data(exclude sensitive data) and your expected result. Thank you.

Best Regards

Rena

[gauravnarchal]

@v-yiruan-msft I have attached the PBIX file for your reference.

I have the invoice date, invoice amount, payment receipt date and amount paid on per transaction basis.

Over a selection of particular date, I need the outstanding invoice per ProfileName. Can you pls help how this can be done?

Age1       1-30

Age2       31-60

Age3       61-90

Age4       90+

Invoice due date is to be calculated from table ClientDetails as per the number of “CreditDays”.

Please let me know if you need any other information.

Looking forward to your feedback/guidance.

Download PBIX

[v-yiruan-msft]

Hi @gauravnarchal ,

What's your desired result? You want to get all invoice number per profileName which the invoice is expired? Could you please explain the calculation logic or function? Outstanding invoice number per profileName=invoice number (invoice date+CreditDays<today)??

Best Regards

Rena

[gauravnarchal]

Thank you @v-yiruan-msft 

I could not find the Ageing measure as I want the result to come under the ageing table as below

Age1 1-30 (Days)

Age2 31-60 (Days)

Age3 61-90 (Days)

Age4 90+ (Days) 

Statement due date logic is as below:-

• If ClientDetail(Statement group) is M, it is a monthly statement, if the ClientDetail(Statement group) is F than its fortnightly statement
• For monthly statement – due date will be 1st date of the following month plus credit days (Example – for 01-31 May statement with 30 days credit, the due date will be 30 Jun which is 01Jun +30 days)
• For Fortnightly statement - due date will be different for 1st FN (01 to 15 Invoice Date) and 2nd FN (16 to 30 or 31 Invoice Date)

• If invoice date is before 16th of the month, then the due date will be 16th + credit days (Example – for 01-15 May statement with 30 days credit, the due date for invoices will be 14th Jun that is 16th May + 30days)
• If invoice date is after 16th of the month, then the due date will be 01st of following month + credit days (Example – for 16-31st May statement with 30 days credit, the due date for invoices will be 30th Jun that is 01st Jun + 30days

Just to simplify - my due date is statement due date and not invoice due date.

Thanks

Gaurav

[gauravnarchal]

@v-yiruan-msft - Any suggestions or views you would like to share?

[amitchandak]

@gauravnarchal , refer if this can help

https://www.credera.com/blog/technology-solutions/creating-aging-report-using-a-user-selected-date-i...

https://powerpivotpro.com/2012/04/aged-debtorsdynamic-banding-in-dax/

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization