Need help on create custom column with condition.

[VictorV]

Need help on create custom column with condition. 

Explain: I have table 2 like below, it have table2[customer code] = table1[customer code] , and table2[code product] sometimes match with table1[code product], because after year it will reset to 1. Then I have table2[Amount] also sometimes will be incorect due to user input wrong, the table1[Amount] is the correct one and the Table2[Date] also sometimes have date on it, but the date is correct, and table2[Date] = table1[Date]

What I need now:

I need create a custome column [Amount] in Table 2, base on condition:

IF table2[code product] = table1[code product], then it will check for table2[customer code] = table1[customer code], then it will check for the Date,

incase 1: the table2[Date] is not have value, then it will check for Amount, and if the Amount is correct it will use that table1[Amount] and the table1[Date]

incase 2: the table2[Date] is appear and = table1[Date], then it will check the table1[Amount] = table2[Amount], in any situation of [Amount], it will always use table1[Amount]

Please help me!!!!!!!!!!!

[ryan_mayu · Accepted solution]

@VictorV 

pls try this

Column =

VAR _check=maxx(FILTER('Table 1','Table 1'[code product]='Table 2'[Code product]&&'Table 1'[Customer code]='Table 2'[Customer Code]),'Table 1'[Amount])

VAR _check2=maxx(FILTER('Table 1','Table 1'[code product]='Table 2'[Code product]&&'Table 1'[Customer code]='Table 2'[Customer Code]&&'Table 1'[Date]='Table 2'[Date]),'Table 1'[Amount])

return if(not(ISBLANK(_check)),if(not(ISBLANK(_check2)),_check2,if(ISBLANK('Table 2'[Date]),_check)))

pls see the attachment below

[VictorV]

Thanks so much!!! it's work perfect

[ryan_mayu]

you are welcome