Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm trying to calculate the age as of an Event Date. I have 2 tables, Table 1 - CustomerDate and Table 2 - EventData. I'm trying to find the age of the participant at the time of event. I tried to do a calculated column with a DATEDIFF function with the birthdate and eventdate, but because the data is in two different tables it's not letting me do that.
Any advice on how to solve this would be greatly appreciated?
Thanks
Solved! Go to Solution.
Hi @Anduve11
Try this calculated column in Events table (Table 2)
Age= DATEDIFF ( LOOKUPVALUE ( Table1[Birthdate], Table1[Customer ID], Table2[CustomerID] ), Table2[EventDate], YEAR )
Actually, nevermind, this function/column did not work. It works if the birtdate happened in the year before the date of service, but if the birthdate was after it was still increasing the age when it shouldn't.
You can try this too. Join both the table on CustomerID. Then in First table create a calculated column
Column = DATEDIFF(Table3[BirthDate],RELATED(Table4[Eventdate]),YEAR)
Solution provided by @Zubair_Muhammad is more easier I think. But this works too
Hi @Anduve11
Try this calculated column in Events table (Table 2)
Age= DATEDIFF ( LOOKUPVALUE ( Table1[Birthdate], Table1[Customer ID], Table2[CustomerID] ), Table2[EventDate], YEAR )
I had this same issue and the calculated column worked, but I have several blank lines where the age is not populating. What would be causing blank lines?
User | Count |
---|---|
93 | |
83 | |
77 | |
74 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |