Forum Discussion
Solved
Cross table Datediff
Hello, Lets say I have a person table where I can find date of birth for every person in the system. I also have an event table where I can find the date an event occured at and another field that ...
What’s the warning message when you try including “RELATED” in the calculate column expression?
If Event and Contacts table has relationship with the PersonID key, you should be able to get the desired result with following column formula.
Age = IF ( ISERROR ( DATEDIFF ( Event[Date], RELATED ( Contacts[DateOfBirth] ), YEAR ) ), DATEDIFF ( RELATED ( Contacts[DateOfBirth] ), Event[Date], YEAR ) )Best Regards,
Herbert
With this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9KTVfSUTLWNzTSN7Q0N1KK1YlWcsrMyQEKgoQMgKKmBmDRoPzk7NQSoLiZvpm+kYGBmVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person = _t, Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Column1", type date}})
in
#"Changed Type"
and this, related on Person (many-to-one Events to People)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci1LzSsxVNJRMjTQNzTVNzIwNABy3ItS05VidXBJB+UnZ6eWIBQYISswwdCPIU1Qv1NmTg5C2hhZ2gwuHQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Event = _t, Date = _t, Person = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Event", type text}, {"Date", type date}, {"Person", type text}})
in
#"Changed Type"
I did this in a column and it worked
Age = DATEDIFF(RELATED(People[Column1].[Date]),Events[Date].[Date],YEAR)