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 calculatio...
Need Help
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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Stachu,

       

      this formula isn't working either. table 2 is a created table with following data:

       

      The formula used is: Table 2 = FILTER(CALENDAR("01-01-2018",TODAY()),[Date]=EOMONTH([Date],0))

       

      Date
      31 January 2018
      28 February 2018
      31 March 2018
      30 April 2018
      31 May 2018
      30 June 2018
      31 July 2018
      31 August 2018
      30 September 2018

       

      Using the formula you mentioned i am only getting new against the dates where live date is end of month. However what i need is this (the client live date of 26th oct is also a new client for october 2018)

      Table 1

       

      TodayLive dateClient IDActual statusStatus required
      26-Oct30-SepA00034newnew
      26-Oct26-Oct12303oldnew
      26-Oct05-Oct12302oldnew
      26-Oct28-Feb564735newnew
      26-Oct01-Sep102538oldnew
      26-Oct15-Sep109928oldnew
      26-Oct31-Jul0928abcnewnew
      26-Oct26-Oct1856XYoldnew
      • Stachu's avatar
        Stachu
        Community Champion
        7 years ago

        based on what you've written I'm not sure whether I get the requirement properly


        are the values in the column supposed to chagne depending on the month you select? if that's the case then it's not possible.
        you could show the customers which are new for a given month with a measure, but it's a different problem

         

        If the values in the column are static - do you mean to compare them with the real time date? e.g. using TODAY()? if that's the case then there is no need for the additional table