Forum Discussion
Total issue
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.
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.
