cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
floriangoerig Frequent Visitor
Frequent Visitor

DAX Date Function (Datediff) cross table

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?
6 REPLIES 6
Super User
Super User

Re: DAX Date Function (Datediff) cross table

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





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




floriangoerig Frequent Visitor
Frequent Visitor

Re: DAX Date Function (Datediff) cross table

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

Super User
Super User

Re: DAX Date Function (Datediff) cross table

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

 

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




Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




Community Support Team
Community Support Team

Re: DAX Date Function (Datediff) cross table

Hi @floriangoerig ,

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
floriangoerig Frequent Visitor
Frequent Visitor

Re: DAX Date Function (Datediff) cross table

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
2019-03-05 09_41_07-Master Report Buchungen - Power BI Desktop.jpg
Community Support Team
Community Support Team

Re: DAX Date Function (Datediff) cross table

Hi @floriangoerig,

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.