Forum Discussion

pmdci's avatar
pmdci
Advocate V
10 years ago
Solved

DAX: relating a field from another table in a function

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 h...
Need Help
  • pmdci's avatar
    pmdci
    10 years ago

    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! :smileysad::smileymad:

Neuro81's avatar
Neuro81
Helper I
10 years ago

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

  • pmdci's avatar
    pmdci
    Advocate V
    10 years ago

    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.

  • pmdci's avatar
    pmdci
    Advocate V
    10 years ago

    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! :smileysad::smileymad:

    • Neuro81's avatar
      Neuro81
      Helper I
      10 years ago

      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!!