Calculating days between 2 tables?


I’ve seen many examples of how to calculate the days between 2 dates when your start finish date is in one table, but how do you calculate it when its start date is in one table and the end date is in another table?




This is one of those “it depends” answers. If the start and end dates conceptually belong in the same table, I’d say move them together via Power Query. If there’s good data modeling reasons for keeping them separate, then it will depend on the nature of the relationship between the two tables (you may be able to use the RELATED function). If there’s no relationship between the tables, then you may want to create a virtual table containing both start and end dates, using the appropriate DAX join function.

If you’re looking for a more specific answer, it would help to post a sample data file to see your particular situation.

Hope this is helpful.

  • Brian

@BrianJ is right. If you can do this in PQ, I’d say do it there. Can easily create a duration column.

Now, since that column will only be updated on refresh time, if you want something a little more dynamic you will have to use a measure. There are a few ways to get both dates. Can use RELATED, USERELATIONSHIP, TREATAS just off the top.

If you have a specific example post it up. As with most things, there is generally not a single best way to do something as there’s always some sort of wrinkle specific to that situation.