Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-piga-msft
Resident Rockstar
Resident Rockstar

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

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.
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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

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

 

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.