Dynamics NAV open entries aging report (Det. Cust. Ledg. Ent.)

[GlmMa]

Dynamics NAV 2018 Business Central (on premise) | PowerBI Desktop

Hi,

I need to create an overview of open customer ledger entries in an aging fashion (age buckets of 30/60/90... days overdue) in order to evaluate our customer's "paying morale" tendencies.

In my assumption I used the Detailed Cust_ Ledg_ Entry table which carries invoices, payment and credit memo entries. In this table I already created two columns. The "Age" column calculates the days an invoice is overdue. The "AgeBucket" column is representing the age category - overdue up to 30/60/90/+ days.

I already created measures for each AgeBucket category. Follwing shows the one carrying invoices up to 30 days overdue:

30Test = 
SUMX(
    FILTER(DetailedCustomerLedgerEntries, 
    DetailedCustomerLedgerEntries[Age] >0 && DetailedCustomerLedgerEntries[Age] <31),
    DetailedCustomerLedgerEntries[Amount (LCY)]
)

The results of above measure are confusing me. For example,

a) for one customer it shows 477,20 which in NAV is not having a Remaining Amount anymore because it has been paid.

b) Sometimes the totals seem to be doubled, not taking the algebraeic signs into account (data types are set to be floats)?

c) Would it be better to check for each initial invoice entry whether it has been closed by a payment entry (what about partial/discount deducted payments)? Sometimes our customers pay multiple invoices in a big chunk and it might not always be applied to each invoices.

Despite of the findings, I generally feel stuck in controlling if an invoice has a remaining amount or not. If someone could give me a hint or a tip on how to proceed I would be thankful.

I desire to create a dynamic chart which shows the historic sum of overdue amounts for the last three months for all customers and on drilldown a chart for each customers. This might be important to know. Hints are welcome here as well.

In case of questions, I will answer quickly during central european working time.

Thank you very much in advance for any help.

GlmMa

[GlmMa]

Dear Winniz,

The solution is working itself - just my problem became more complex and has to be solved in another way.

Could you please elaborate the filter section of the measure?

SUM(DetailedCustomerLedgerEntries[Amount(LCY)]), //summing Amount (LCY) field
    DetailedCustomerLedgerEntries[Age] >= 0, //for age 0 and above
    DetailedCustomerLedgerEntries[Age] < 31, // until age 31

This part is understood.

I am having difficulties understanding the filter section.

FILTER(
        ALLEXCEPT( DetailedCustomerLedgerEntries, DetailedCustomerLedgerEntries[CustomerID] ), //All further applied filter will not take CustomerID into account?

Also with this part.

DetailedCustomerLedgerEntries[Posting Date] <= MAX(DetailedCustomerLedgerEntries[Posting Date]

Filtering Posting Dates that are smaller or equal to the maximum value of Posting Dates. Is this done to basically just include all Posting Dates? And that would apply to a separate date filter, say if I wanted to filter posting dates up to a certain date (or range)?

I will chunk up my requirements in smaller questions as the basics have apparently not arrived yet. Hope to find most of them. Anyway, I will accept your post as solution. Thanks

[v-kkf-msft]

Hi @GlmMa ,

Measures are dynamic calculation formulas where the results change depending on context.

The ALLEXCEPT function will only retain the filters that have been used for the specified column (CustomerID column).

DetailedCustomerLedgerEntries[Posting Date] <= MAX(DetailedCustomerLedgerEntries[Posting Date]    where MAX is used to store the current row value of the Posting Date column.

1. For the first row, it will filter out the rows in the DetailedCustomerLedgerEntries table that meet
          CustomerID is "B",

          0 <= Age < 30,

          and Posting Date <= 6/28/2021,

that is: 

CustomerIDDocument TypeOriginal AmountAmount(LCY)Remaining AmountDue DatePosting Date

B

Invoice

477.2

477.2

0

7/13/2021

6/28/2021

Then sum( [Amount(LCY)] ) = 477.2

2. For the second row, it will filter out the rows in the DetailedCustomerLedgerEntries table that meet
          CustomerID is "B",

          0 <= Age < 30,

          and Posting Date <= 7/9/2021,

that is: 

CustomerIDDocument TypeOriginal AmountAmount(LCY)Remaining AmountDue DatePosting Date

B	Payment	-477.2	-477.2	0	7/9/2021	7/9/2021
B	Invoice	477.2	477.2	0	7/13/2021	6/28/2021

Then sum( [Amount(LCY)] ) = 477.2 + (-477.2) = 0

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft · Accepted solution]

Hi @GlmMa ,

Try the following formula:

30Test = 
CALCULATE(
    SUM(DetailedCustomerLedgerEntries[Amount(LCY)]),
    DetailedCustomerLedgerEntries[Age] >= 0,
    DetailedCustomerLedgerEntries[Age] < 31,
    FILTER(
        ALLEXCEPT( DetailedCustomerLedgerEntries, DetailedCustomerLedgerEntries[CustomerID] ), 
        DetailedCustomerLedgerEntries[Posting Date] <= MAX(DetailedCustomerLedgerEntries[Posting Date])
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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