Total issue

[Tamuna]

I have this code that has to show 181+ overdue debt for each customer from customers table that has one to many relationship to the sales, return and payment tables. visualization is matrix and filter is unique dates, that filters the visual correctly and also has one to many relationship to all those 3 fact tables. I've tried summarize, sumx, hasonevalue, isincope nd other staff, but nothing shows total as the total of rows correctly. heres the logic Im using

Debt 181+ =

VAR MaxDate = MAX('Date'[Date])

VAR SalesRT =

    CALCULATE(

        SUM(Sales[Amount]),

        BIProdTG[IdProdTG] = 71,

        ALL('Date'),

        Sales[Due Date] <= MaxDate - 181

    )

VAR ReturnRT =

    CALCULATE(

        SUM(Return[Amount]),

        BIProdTG[IdProdTG] = 71,

        ALL('Date'),

        'Date'[Date] <= MaxDate

    )  

VAR PaymentRT =

    CALCULATE(

        SUM(Payment[Amount]),

        Payment[IdSalT] IN {4003, 4004, 4010, 4015, 4005, 4007, 4008, 4009, 4011, 4012, 4013, 4014},

        BIProdTG[IdProdTG] = 71,

        ALL('Date'),

        'Date'[Date] <= MaxDate

    )

VAR Debt =

    IF(SalesRT - ReturnRT - PaymentRT > 0, SalesRT - ReturnRT - PaymentRT, 0)

RETURN Debt

please help me rewrite the logic to show correct totals. and also if I need to add types of organizations or regions in visualization that also should not be problem too.

[DataNinja777]

Hi @Tamuna ,

I would have expected your ERP system's default AR module to produce the open AR balance as of a specific date without requiring you to compile that information manually. It seems like the data you are working with consists of transaction lists rather than an open items list for AR. Typically, once collections from customers are recorded, the AR module (well, AR accountants) clears the sales and returns by matching them with the corresponding payments, resulting in a much smaller list of open (uncollected) items.

My recommendation for calculating the overdue balance is by using the open items list extracted directly from your company’s ERP's AR module. This approach will simplify the calculations significantly.

Best regards,

[Tamuna]

Hi,

We don't have an ERP system. I work with raw tables for AR data.

[DataNinja777]

Hi @Tamuna,

How can you manage millions of rows of data without an ERP system? Excel can only handle up to 1,048,576 rows. Just curious!

Best regards,

[Tamuna]

We manage millions of rows of data using a SQL database, along with software that generates the database. However, this software is not an actual ERP system.

[DataNinja777]

Hi @Tamuna ,

First, I recommend reorganizing your fact table. Currently, it appears that sales, returns, and payments are recorded in separate columns. I suggest combining them into a single column, with returns and payments recorded as negative values. This way, you can easily sum the combined column to calculate the receivable amount.

Next, instead of using hardcoded filters like BIProdTG[IdProdTG] = 71, I recommend using a more flexible approach, such as SELECTEDVALUE(BIProdTG[IdProdTG]). This will allow you to filter by different product IDs without needing to modify the formula each time.

Additionally, the condition Payment[IdSalT] IN {4003, 4004, 4010, 4015, 4005, 4007, 4008, 4009, 4011, 4012, 4013, 4014} can be optimized to make it more maintainable, especially if adjustments are needed in the future.

I also suggest creating a relationship between the due date and the calendar table. While the relationship itself doesn’t calculate overdue amounts, it provides the foundation for calculating the difference between the data extraction date and the due date, giving you row-by-row insights into the overdue status of accounts receivable.

By applying these adjustments, you can avoid relying on IF formulas, which are currently causing calculation errors. Instead, a straightforward summation will correctly determine the overdue amounts exceeding 181 days.

[Tamuna]

Thank you for your support, but each of those fact tables contains millions of rows, and I have already built hundreds of measures using them. I’m afraid adding an additional table combining those tables will cause additional performance problems. I’ll wait a little for other tips, and if there’s no way out, I’ll try your approach. I already have an inactive relationship between the due date and the calendar table.

[Kedar_Pande]

@Tamuna 

You can try-

Debt 181+ =
VAR MaxDate = MAX('Date'[Date])

VAR SalesRT =
CALCULATE(
SUM(Sales[Amount]),
Sales[Due Date] <= MaxDate - 181,
BIProdTG[IdProdTG] = 71,
ALL('Date')
)

VAR ReturnRT =
CALCULATE(
SUM(Return[Amount]),
'Date'[Date] <= MaxDate,
BIProdTG[IdProdTG] = 71,
ALL('Date')
)

VAR PaymentRT =
CALCULATE(
SUM(Payment[Amount]),
Payment[IdSalT] IN {4003, 4004, 4010, 4015, 4005, 4007, 4008, 4009, 4011, 4012, 4013, 4014},
BIProdTG[IdProdTG] = 71,
'Date'[Date] <= MaxDate,
ALL('Date')
)

VAR Debt =
SalesRT - ReturnRT - PaymentRT

RETURN 
IF(
HASONEVALUE(Customers[CustomerID]), 
IF(Debt > 0, Debt, 0), 
SUMX(VALUES(Customers[CustomerID]), IF(SalesRT - ReturnRT - PaymentRT > 0, SalesRT - ReturnRT - PaymentRT, 0))
)

If this doesn't give you the result, please share the file to have a closer look.

⚠️If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Tamuna]

Thank you, Kedar, for your support, but that’s not working for me. The PBI file is too complicated, and the names are also not in English. I’ll try to create a suitable sample later.