Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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!
What errors did you get?
also did you create a relationship between the two tables in the diagram view?
@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!
@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!!
@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 You can make use of RELATED function. It will work since you have relation between two tables.
@ankitpatira as I mentioned in my original post, I tried it but I only got errors.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
120 | |
112 | |
60 | |
58 |