I am currently working on an AR aging report, everything was working fine except one last bit that has been holding me up for the past few days. So I am here looking for help.
Essentially I am working for a construction company, and our customer usually retains a certain amount before a contracted period has pass and nothing has happened to the work done. And for each invoice, I have that retention amount. However, the problem comes to my aging when sometimes our customer would pay a partial amount of retention and net invoice amount are actually smaller than the retention amount.
So I need a Dax to tell me if the net amount of an invoice is smaller than the set retention, if so use the net amount, otherwise use the set retention amount. The result of the amount will be used to calculate the aging.
Taking the following invoice for example:
Transaction PJR004905 is consisting of two amounts$85,943.87 the invoice amount and $(81,659.85) the payment amount, the net came to $4,284.02 but the retention amount is showing $4,297.19, which is the original amount. Now I need a Dax to use 4,284.02 instead of $4,297.19.
I tried to use an if statement, it would make the entire page slow and uncalculable.
Please let me know if you have any idea how to solve this.
Hi @dcjiang , we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include the masked demo pbix file and details of how you want to visualize a result, and any other supporting links and details.
without access to your model, as mentioned in the post above, it’s hard to know exactly what is slowing your report so much.
That being said, have you tried using a SWITCH/TRUE statement?
similar to an IF statement, the SWITCH/TRUE combination looks for the first true statement and returns that result.
So, in this type of an instance, it would look something like this:
Basically it’s
checking to see if NetAmt is lower than Retention Amt - and if that’s true it returns NetAmt
if that’s false, it returns the Retention Amt
the nice thing about SWITCH/TRUE is that it nests much more easily than a bunch of IF statements
Example
= SWITCH( TRUE(),
Check Condition A, if Condition A is true Return Result A,
Check Condition B, if Condition B is true Return Result B,
Check Condition C, if Condition C is true Return Result C, if none of the above is true, Return Result D )
There’s another technique on offer and that’s using MINX.
Only prerequisite is that you have both the payment- and retention amounts as Measures. Create a one column virtual table that lists your measures using the curly brackets, the name for that column is always “Value”, like shown below.
The Min Value =
MINX(
{ [payment amount], [retention amount] },
[Value]
)
Hi @dcjiang, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Hi @dcjiang, we’ve noticed that no response has been received from you since the 26th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
