cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Patron
Post Patron

Debtors ageing measure

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?

6 REPLIES 6
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@yingyinr 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

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you @yingyinr 

 

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

@yingyinr - Any suggestions or views you would like to share?

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors