DAX: relating a field from another table in a function

[pmdci]

Hi,
 
I created the following DAX calculated column, which I am using across different tables.
 
=(YEAR(Today())-YEAR(DATEFIELD]))*12+MONTH(Today())-MONTH([DATEFIELD])
 
This field calculates how many months have elapsed from the date found in DATEFIELD until the time the model is processed. It works quite well, except for one little caveat: The DATE FIELD must exist within the table itself.
 
But what if I want to relate to a date found at a date dimension table? Say that I only have a datekey in the table I want to hold my calculated field, and I want to refer to the date field found in the date dimension table. How would I go about it? I tried playing with the RELATED() function but I only got errors.
 
Regards,
P.

[Neuro81]

What errors did you get?
also did you create a relationship between the two tables in the diagram view?

[pmdci · Accepted solution]

@Neuro81 and @ankitpatira

Sorry for the trouble, but I managed to fix it in the end

=(YEAR(Today())-YEAR(RELATED('Availability Calendar'[Date])))*12+MONTH(Today())-MONTH(RELATED('Availability Calendar'[Date]))

The problem is that I got lost with the number of parenthesis (how lame!) So I deconstructed the formula and use a replace function to make it work.

Wish I could delete this post altogether. Again sorry for wasting your time! 

[Neuro81]

@pmdci
Sometimes its best to talk it out.

Whenever I get stuck I usually take a walk and try not to think of the problem and a lot of the time the solution just comes.

Its good that you take the time to try and fix the problem yourself instead of just waiting till someone comes to help you!!

[pmdci]

@Neuro81 There is a relationship, yes. Between the fact table in which the calculated column is located, and a Date Dimension table. The linked fields are called DATEKEY in both tables.

Here is what I tried:

=(YEAR(Today())-YEAR(RELATED('Availability Calendar'[Date]))*12+MONTH(Today())-MONTH(RELATED('Availability Calendar'[Date]))

But Visual Studio gives me a syntax error message.

[ankitpatira]

@pmdci You can make use of RELATED function. It will work since you have relation between two tables.

[pmdci]

@ankitpatira as I mentioned in my original post, I tried it but I only got errors.

[ankitpatira]

@pmdci what is the error you getting when using RELATED.

[pmdci]

@ankitpatira I get a syntax error. See above.

Regards,

P.