DAX Date Function (Datediff) cross table

[Anonymous]

Hi there,

I have two tables as sources in my report. Now I want to use datediff function on two dates one located in on the other in the other table. Somehow it doesnt seem to support this. 

Days Subscribe/Book = DATEDIFF(Table1[dateCreated];Table2[Opt-in time];day)

Autocomplete just doesnt offer my the Table2, although tables are correcty joined.

Any Idea?

[v-piga-msft]

Hi @Anonymous ,

By my tests, the suggestion of parry2k should be helpful.

If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your data sample and your desired output so that we could help further on it.

Best Regards,

Cherry

[parry2k]

@Anonymous  is it one to many relationship? if yes, you can use RELATED function to get date from one side of table relationship.

[Anonymous]

@parry2k Hi there, yes it is a one to many relationship. How will then the syntax look like in my example?

[parry2k]

@Anonymous  try following, assuming table2 is on one side of the relationship.

Days Subscribe/Book = DATEDIFF(Table1[dateCreated];related(Table2[Opt-in time]);day)

[Anonymous]

Hi @parry2k 

Days Subscribe/Book = DATEDIFF(Subscriber[CONFIRM_TIME];related(Buchungen[dateCreated]);day)

This is the formula that I ended up with however PowerBi comes up with an error saying that column Buchungen(dateCreated) is not found or not related to a table in this context. 

What can be the reason? pls. find the relation  setting below (sorry it's german)

Thx Florian

[v-piga-msft]

Hi @Anonymous,

By my tests, I cannot reproduce your issue.

If it is convenient, could you share a dummy pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Cherry