Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi!
So I have a table with acccounts receivable info per account that has the following columns:
I'm trying to clasify their type fo debt based on several conditions. I had previously calculated this in the same Excel table with this formula:
=IF(IF([@group]<>"CASH",SUMIF([accountNumber],[@accountNumber],[debtUSD]))<0,"Credit Balance",IF(AND([@[debtUSD]]<0,OR([@[invoiceType]]="AA",[@[invoiceType]]="BB",[@[invoiceType]]="CC",[@[invoiceType]]="DD",[@[invoiceType]]="EE",[@[invoiceType]]="WV")),"Unapplied Payment",IF(OR([@[collectionsAgent]]="JohnDoe"),"Legal",[@[timeInterval]])))
In summary, the formula will calculate and classify as "Credit Balance", "Unapplied Payment" or "Legal" depending on the group, the amount of debt and the invoice type. Else, it will return the aging of the debt previously calculated on the column timeInterval (if it's 1-30 days old, 31-60, etc).
I was wondering if there is an equivalent expression in DAX I can use. I would really appreciate the help guys 🙂
Solved! Go to Solution.
OK.
Classification = var __currentAccountNumber = 'Accounts Receivable'[Account Number] var __currentDebt = 'Accounts Receivable'[Debt USD] var __currentInvoiceType = 'Accounts Receivable'[Invoice Type] var __currentTimeInterval = 'Accounts Receivable'[Time Interval] var __sumOfDebtForCurrentClient = SUMX( FILTER( 'Accounts Receivable', 'Accounts Receivable'[Account Number] = __currentAccountNumber ), 'Accounts Receivable'[Debt USD] ) var __classification = SWITCH( TRUE(), __sumOfDebtForCurrentClient < 0, "Credit Balance", __currentDebt < 0 && __currentInvoiceType in {"AA", "BB", "CC", "DD"}, "Unapplied Payment", // else __currentTimeInterval ) return __classification
This is your formula for the Classification Column in your table.
If this is not performant enough, then here's a variation on this topic. Just change these lines:
var __sumOfDebtForCurrentClient = SUMX( FILTER( 'Accounts Receivable', 'Accounts Receivable'[Account Number] = __currentAccountNumber ), 'Accounts Receivable'[Debt USD] )
to these
var __sumOfDebtForCurrentClient = CALCULATE( SUM( 'Accounts Receivable'[Debt USD] ), ALLEXCEPT( 'Accounts Receivable', 'Accounts Receivable'[Account Number] ) )
Not sure which one would be faster on a big table.
By the way, here's the Excel formula (which seems to implement a different logic than the one you've described), courtesy of http://excelformulabeautifier.com/:
=IF( IF( [@group] <> "CASH", SUMIF( [accountNumber], [@accountNumber], [debtUSD] ) ) < 0, "Credit Balance", IF( AND( [@[debtUSD]] < 0, OR( [@[invoiceType]] = "AA", [@[invoiceType]] = "BB", [@[invoiceType]] = "CC", [@[invoiceType]] = "DD", [@[invoiceType]] = "EE", [@[invoiceType]] = "WV" ) ), "Unapplied Payment", IF( OR( [@[collectionsAgent]] = "JohnDoe" ), "Legal", [@[timeInterval]] ) ) )
But I've implemented the logic you've described 🙂
Best
Darek
By the way, please disentangle the logic of the Excel formula. I don't think anyone would want to do it themselves - too much work. You have to state the algorithm in a clear, unambiguous way so that the rules for calculation are easily understood. Then and only then will people start working on this 🙂
Best
Darek
Okay so basically the table returns all the invoices the company has in accounts receivable, which means that one accountNumber can have several invoices. Taking that into account the pseudo code would be something like this
If the sum of the debt per client < 0 (SumIf in the excel formula above to sum all the debt from the same customer)
Then client has a "credit balance"
Else if debtUSD < 0 and document type is AA or BB or CC or DD
Then client has "Unapplied Payment"
Else return same value as the column timeInterval
I hope this is helpful and thanks for the tip about Switch function
OK.
Classification = var __currentAccountNumber = 'Accounts Receivable'[Account Number] var __currentDebt = 'Accounts Receivable'[Debt USD] var __currentInvoiceType = 'Accounts Receivable'[Invoice Type] var __currentTimeInterval = 'Accounts Receivable'[Time Interval] var __sumOfDebtForCurrentClient = SUMX( FILTER( 'Accounts Receivable', 'Accounts Receivable'[Account Number] = __currentAccountNumber ), 'Accounts Receivable'[Debt USD] ) var __classification = SWITCH( TRUE(), __sumOfDebtForCurrentClient < 0, "Credit Balance", __currentDebt < 0 && __currentInvoiceType in {"AA", "BB", "CC", "DD"}, "Unapplied Payment", // else __currentTimeInterval ) return __classification
This is your formula for the Classification Column in your table.
If this is not performant enough, then here's a variation on this topic. Just change these lines:
var __sumOfDebtForCurrentClient = SUMX( FILTER( 'Accounts Receivable', 'Accounts Receivable'[Account Number] = __currentAccountNumber ), 'Accounts Receivable'[Debt USD] )
to these
var __sumOfDebtForCurrentClient = CALCULATE( SUM( 'Accounts Receivable'[Debt USD] ), ALLEXCEPT( 'Accounts Receivable', 'Accounts Receivable'[Account Number] ) )
Not sure which one would be faster on a big table.
By the way, here's the Excel formula (which seems to implement a different logic than the one you've described), courtesy of http://excelformulabeautifier.com/:
=IF( IF( [@group] <> "CASH", SUMIF( [accountNumber], [@accountNumber], [debtUSD] ) ) < 0, "Credit Balance", IF( AND( [@[debtUSD]] < 0, OR( [@[invoiceType]] = "AA", [@[invoiceType]] = "BB", [@[invoiceType]] = "CC", [@[invoiceType]] = "DD", [@[invoiceType]] = "EE", [@[invoiceType]] = "WV" ) ), "Unapplied Payment", IF( OR( [@[collectionsAgent]] = "JohnDoe" ), "Legal", [@[timeInterval]] ) ) )
But I've implemented the logic you've described 🙂
Best
Darek
Hello Darek, just a quick follow up:
Power Bi is giving me an issue with the declared variables with the following error:
A single value for column 'Account Number' in table 'Accounts Receivable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Same thing happens with the other variables. Do you happen to know any fix for this?
Regards
I did say: This is your formula for the Classification column in your table. This is not a measure.
Are you trying to use any of the formulas as measures? If you do, then you'll get this error.
One last thing: READ WHAT I'VE WRITTEN BEFORE WELL AND TRY TO UNDERSTAND IT. Then you'll have no problems. When you read things, read them carefully with a full understanding. Don't glance over text. READ UNTIL YOU FULLY UNDERSTAND WHAT'S BEEN EXPRESSED IN THERE.
Best
Darek
Hi Darek!
Yeah I see the mistake. When I was trying to add it as a column it returned the error "Token Eof expected". But I was trying to add the column from the query editor rather than with the Add column button in the ribbon tab, in my mind they were the same thing.
Thanks again now it's fully working.
Regards
Good 🙂
Best
Darek
It's working fine! Both sumx and switch formulas were really helpful and that excel formula beautifier will be helpful.
Thankyou,
MS
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
34 | |
14 | |
14 | |
12 | |
9 |