Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Nested if and statement based on dates between

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?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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" ) )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.