Forum Discussion
If Statement Total does not add up
Hi,
I know there have been lots of questions about this topic already but I just can't figure out how to apply the solutions to my own problem.
I have this IF statement here:
calc =
IF(
MAX(DateTable[Exp_Dates]) >= MAX(A[Closed_Date])
, [USD_AP] - [USD_FC]
, [USD_AP]
)
When now building a table I get this:
But what I want is the column sum of the calculated values displayed, so 155MM + .591MM. How do I do that?
Thanks!
Anonymous,
Here's an example you can adapt to your model. It evaluates the IF statement on a yearly basis. If you need it to evaluate at the day level, change "Year" to "Date".
calc = SUMX ( VALUES ( DateTable[Year] ), IF ( DateTable[Year] >= 2024, [USD_AP] - [USD_FC], [USD_AP] ) )Replace the hardcoded "2024" with the correct expression. I'm not sure what your table "A" is and how it's related to your model. The measure USD_AP has an incorrect total in your screenshot.
6 Replies
Anonymous,
Try this measure. It iterates DateTable, so totals will be the sum of parts. Not sure if you need MAX for A[Closed_Date] since this may depend on your business logic.
calc = SUMX ( DateTable, IF ( DateTable[Exp_Dates] >= A[Closed_Date], [USD_AP] - [USD_FC], [USD_AP] ) )Excellent article on totals:
https://www.sqlbi.com/articles/obtaining-accurate-totals-in-dax/
Anonymous,
Are you able to provide sample data (table format or pbix link), screenshots of your model, and business logic?