Debtors ageing measure - Power Bi

[gauravnarchal]

I'd be extremely grateful if someone could help with this.

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. Invoice due date is to be calculated from table ClientDetails as per the number of “CreditDays”. Can you pls help how this can be done?

Age1       1-30

Age2       31-60

Age3       61-90

Age4       90+

Please let me know if you need any other information.

Looking forward to your feedback/guidance.

I have attached the PBIX file for your reference.

Download PBIX

[Greg_Deckler]

@gauravnarchal - Not entirely sure. Are you saying that you want to caculate invoice due date and then calculate how many days past due based upon TODAY?

[gauravnarchal]

@Greg_Deckler - Yes instead of upon today, it should be as of the selected date, as we may need to pull report as of the past date

I have attached the PBIX that I am using in my post earlier.

Thank You!

[gauravnarchal]

@Greg_Deckler  - Do you think I can get the desired results? Any help on this would be appreciated.

Many thanks in advance.

[amaleranda]

Spoiler

@gauravnarchal 

 I have created below columns in your ARInvoices tabel to get status based on credit days, 

Credit Days =

LOOKUPVALUE(ClientDetail[CreditDays],ClientDetail[ProfileNumber],ARInvoices[ProfileNumber])

Due Date =

ARInvoices[InvoiceDate]+ARInvoices[Credit Days]

Status =

IF(ARInvoices[Due Date] > TODAY(), "Not Due", "Overdue")

from there you can find out number of overdue invoices by Profile Name

[gauravnarchal]

Thank you @amaleranda 

I have now created the columns as advised by you.

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

[sturlaws]

Hi @gauravnarchal,

what is your desired output? To label each Profile/InvoiceNumber with an age? Or to find the number of profiles/InvoiceNumbers per age? Or the amount not paid in each age?

I have created a measure that returns the age of each invoice number, you find an updated version of your file below.

Beware that I changed a little on the datamodell as well, because your model was unnecessary complex.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[gauravnarchal]

Thank you for your help  @sturlaws

I want the statement due logic 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.

[sturlaws]

Hi @gauravnarchal

I made an updated version of your sample report, according to your requirements. I implemtented it as a calculated column in the ARInvoices table.

So depending on your use case, you can drag the new column into a visual to split whatever measurement you want to analyse based on the Age. Or drag the Age column into a table visual to see the age of each invoice.

Cheers,
Sturla

[gauravnarchal]

@sturlaws  - We have achieved 50% with your help and would need some more assistance.

1)         Slicer should Calendar (Table) instead of InvoiceDate.

2)         For (_hasNotPaid) you did not consider the ReceiptDate as the ageing of the Invoices is linked when the payment is made.

If Selected Calendar date is < then Receipt date then the invoice is “Unpaid” or If there is no receipt date then invoice is “Unpaid”

I have reattached the PBIX file.

Appreciate all your help in advance.

Download PBIX

[sturlaws]

This is a bit tricky, because CashReceiptInvoices is related with CashReceipt with a many-to-many-relationship. 

I think it is doable, but the data in your report does not really match up. There are only 2 invoices in ARInvoices which have a match in ARInvoiceDetails. Which means there is not enough data to test a solution. 

But rather than fixing this in Power BI, the modelleing should be approved, e.g. [AmountPaid] should be in the CashReceipt-table. Or [ReceiptDate] in CashReceiptInvoices.

What is your source? I see that in this report you are importing from Excel, is this how your data is actually shaped? Or do you have the possibility to shape the data differently?

[gauravnarchal]

Hi @sturlaws  - I know this is tricky 😊. Our data source is SQL and cannot share the same file so have created a new file and uploaded the excel the same way how the SQL tables are.

Unfortunately, I will not be able to change the modelling as the data is from SQL.

I have now updated the data and attached is the updated PBIX.

Download PBIX

Thanks

Gaurav

[gauravnarchal]

@sturlaws Please let me know if you need any more information to achieve  this requirement.

Once again many thanks for your help in advance.

[sturlaws]

Hi @gauravnarchal 

I have attached an updated version

[gauravnarchal]

Thank you for your help @sturlaws 

Currently the results is showing as a Measure, how can I show them in Matrix? I have attached the revised PBIX

Download Here

[sturlaws]

sure

[gauravnarchal]

@sturlaws  - Many thanks for your help. I think the new measure is missing the link

1. As of 31st July - Measure 1 total is correct but Measure 2 total result is wrong. Also for any other "as on selected date", same issue.

2. Using Measure 2 - I cannot get "total" while using Matrix visual. Need row and column total.

Updated PBIX is attached

Download PBIX

[sturlaws]

try to change measure 2 to this:

Measure 2 =
VAR _ageCategory =
    CALCULATE ( MAX ( AgingTable[Index] ) )
RETURN
    SUMX (
        VALUES ( ARInvoices[InvoiceID] ),
        VAR _age = [Measure]
        VAR _amount =
            CALCULATE ( SUM ( ARInvoices[InvoiceAmount] ) )
        RETURN
            SWITCH (
                TRUE (),
                _age = "Age 1"
                    && _ageCategory = 1, _amount,
                _age = "Age 2"
                    && _ageCategory = 2, _amount,
                _age = "Age 3"
                    && _ageCategory = 3, _amount,
                _age = "Age 4"
                    && _ageCategory = 4, _amount
            )
    )

[gauravnarchal]

@sturlaws 

1) Row total is still not reflecting

2) Column total is incorrect

Thank You!

[sturlaws]

@gauravnarchal 

sorry for slow response time, very busy at work these days.

This seems to return the correct row totals:

Measure 2 =
VAR _ageCategory =
    CALCULATE ( MAX ( AgingTable[Index] ) )
VAR _tmp =
    SUMX (
        VALUES ( ARInvoices[InvoiceID] ),
        VAR _age = [Measure]
        VAR _amount =
            CALCULATE ( SUM ( ARInvoices[InvoiceAmount] ) )
        RETURN
            SWITCH (
                TRUE (),
                _age = "Age 1"
                    && _ageCategory = 1, _amount,
                _age = "Age 2"
                    && _ageCategory = 2, _amount,
                _age = "Age 3"
                    && _ageCategory = 3, _amount,
                _age = "Age 4"
                    && _ageCategory = 4, _amount
            )
    )
RETURN
    IF (
        ISFILTERED ( AgingTable[AgingGroup] ),
        _tmp,
        SUMX (
            VALUES ( ARInvoices[InvoiceNumber] ),
            VAR _selectedDate =
                CALCULATE ( MAX ( 'Calendar'[Date] ) )
            VAR _dueDate =
                CALCULATE ( MAX ( ARInvoices[DueDate] ) )
            VAR _invoiceDetailIDs =
                CALCULATE ( MAX ( ARInvoiceDetails[InvoiceDetailID] ) )
            VAR _receiptDate =
                CALCULATE (
                    MAX ( CashReceipt[ReceiptDate] ),
                    FILTER ( ALL ( CashReceipt ), CashReceipt[InvoiceDetailID] = _invoiceDetailIDs )
                )
            RETURN
                CALCULATE (
                    SUM ( ARInvoices[InvoiceAmount] ),
                    FILTER (
                        ALL ( 'ARInvoices'[InvoiceDate] ),
                        _selectedDate > _dueDate
                            && _receiptDate > _selectedDate
                    )
                )
        )
    )

[gauravnarchal]

@sturlaws  - Please let me know if you need any more information and thank you for your help in advance.