Forum Discussion

Tamuna's avatar
Tamuna
Frequent Visitor
1 year ago

Total issue

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 filte...
Need Help
Kedar_Pande's avatar
Kedar_Pande
Super User
1 year ago

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's avatar
Tamuna
Frequent Visitor
1 year ago

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.