Nested if and statement based on dates between

[Anonymous]

Hi, 

I'm attempting to write a nested if an statement that references 2 tables. The statement should state IF(isblank[table1criteria1]),[table1criteria2],if([table1criteria2] date is between [table2criteria] then return [table2criteria2]) otherwise if false return "Non-Reservation"

Essentially I want the formula to return a criteria based on a value in table 1 being between 2 dates in a value in table 2. It'd return a value from table 2. 

It won't let me reference 2 different tables. Is there another solution?

[Anonymous · Accepted solution]

Hi @Anonymous ,

Can you provide some sample data and expected results?

From what I understand, your two tables look like

You could create a calculated table to judge

Table = 
ADDCOLUMNS (
    CROSSJOIN ( 'table1', 'table2' ),
    "Column",
        IF (
            [criteria1] = BLANK (),
            [criteria2],
            IF (
                [date] < [date2]
                    && [date] > [date1],
                [table2criteria2],
                "Non-Reservation"
            )
        )
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Assume you need a new column

new column =

var _cnt = maxx(filter(Table2, Table1[Date] >= Table2[Date1] && Table1[Date1] <= Table2[Date2] ) ,Table2[table2criteria2] )

return

IF(isblank[table1criteria1]),[table1criteria2] , if(not(Isbalnk(_cnt)) , _cnt , "Non-Reservation" ) )