Hello friends, Hope all is well! please help me create a DAX formula.
As you can see in the below image, I need the formula to place an X mark if there are matching fields at the
last column on the rows that have the same:
A. Invoice Number.
B. Branch Name.
C. The amount matches but one in positive and the other is negative.
Thank you in advance for your time & efforts.
If I understand correctly, you could use this:
=IF(COUNTROWS(FILTER(Table1,[Invoice]=EARLIER([Invoice])&&[Branch]=EARLIER([Branch])&&[Amount]=-EArlier([Amount])))>0,"X",blank())
Dear Velouria, that was awesome, may I please ask for one more modification?
if amount results are ZERO no X. the formula should only work on netting out figures, e.g. 150 VS -150 etc
Thank you so much
You should be able to just wrap another IF:
=IF([Amount]<>0,IF(COUNTROWS(FILTER(Table1,[Invoice]=EARLIER([Invoice])&&[Branch]=EARLIER([Branch])&&[Amount]=-EArlier([Amount])))>0,"X",blank()),BLANK())
Truly a ROCKSTAR! Helped me learn something new