Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Create a Calculated Column from Different Tables

Hi,

 

I have data as in the table below:

I need period end dates (month end dates from jan to dec 2018). I was able to create a new table with the required dates. However for my further calculations, I need to match my live date in existing data to calculated period end date.

 

For example, if I need to know who my new client for a particular month is, so I will use the logic:

=If(Table1[livedate] = table2[period end date], “new”)

 

ISSUE: The formula bar doesn’t let me use table 2 data and I am unable to create any further column or measure. I have created the relationships between 2 feilds and still isnt working (This can easily be done in Tableau by creating a parameter). Can someone please help!.

 

Thanks in advance.

 

Today

Live date

Client ID

30-Oct

01-Sep

A00034

30-Oct

26-Oct

12303

30-Oct

26-Oct

12302

30-Oct

25-Oct

564735

30-Oct

01-Sep

102538

30-Oct

01-Sep

109928

30-Oct

01-Sep

0928abc

30-Oct

26-Oct

1856XY

 

Need Help

10 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

    if there is a join between the tables (e.g. 1:1, 1:many) you can reference them with:
    RELATED when referring to the 1

    RELATEDTABLE when referring to the many

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Stachu,

       

      I'll rephrase my statement.

       

      I need to match year and month of live date and period end date. I am using the following formula (using related function) but it is not populating any data.

       

      Column = IF(AND(YEAR(Table[LiveDate]) = YEAR(RELATED('Table2'[PeriodEndDate]), MONTH(Table[LiveDate]) = MONTH(RELATED('Table2'[PeriodEndDate], "New")

       

      Can't find the error

      • Stachu's avatar
        Stachu
        Community Champion
        7 years ago

        you're missing few closing brackets and false value for IF, this should work

        Column =
IF (
    AND (
        YEAR ( Table[LiveDate] ) = YEAR ( RELATED ( 'Table2'[PeriodEndDate] ) ),
        MONTH ( Table[LiveDate] ) = MONTH ( RELATED ( 'Table2'[PeriodEndDate] ) )
    ),
    "New",
    "Old"
)

        if the code above doesn't work:

        1) are both Table[LiveDate] and Table2[PeriodEndDate] of type date?
        2) is there a relationship between the 2 tables?
        3) can you share the sample of Table2 data?