Forum Discussion
Subtract Two Dates on Inactive Relationship
I am trying what I think will be an easy subtraction but apparently I am not going down the right path. In TableA I have the Purchase Date of ProductID and in TableB I have the Sales Date of ProductID. I want to calculate how many days between those two dates. TableA and TableB have an inactive relationship. I am trying to use combinations of DateDiff and UseRelationship but no joy. Lots of examples out there where folks are using "Calculate" to determine a sales amount of something like that. But, I just want a count of days between the two dates. Any thoughts?
Hi,
Here's what i think you should do:
- Create a Calendar Table and build a relationship from the Date column of the Purchase and Sales table to the Date column of the Calendar Table
- Create a Products table with a single column listing all unique Product ID's. Build a relationship from the Product ID column of the Purchase and Sales table to the Product ID column of the Products Table
- Remove all/any relationship between the Product and Sales table
- To your visual, drag Product ID from the Products Table and Date column from the Calendar Table
- Write these measures
Purchase Date = MIN(Purchases[Date of Purchase])
Sale Date = MIN(Sales[Date of Sale])
Difference = [Sale Date]-[Purchase Date]
Hope this helps.
3 Replies
Hi,
Here's what i think you should do:
- Create a Calendar Table and build a relationship from the Date column of the Purchase and Sales table to the Date column of the Calendar Table
- Create a Products table with a single column listing all unique Product ID's. Build a relationship from the Product ID column of the Purchase and Sales table to the Product ID column of the Products Table
- Remove all/any relationship between the Product and Sales table
- To your visual, drag Product ID from the Products Table and Date column from the Calendar Table
- Write these measures
Purchase Date = MIN(Purchases[Date of Purchase])
Sale Date = MIN(Sales[Date of Sale])
Difference = [Sale Date]-[Purchase Date]
Hope this helps.
collinq , do you something like order id common between two tables. You can have a common dimension and do it
refer almost same problem solved here
Hi amitchandak, thanks for your response but that does not really get me there. I am not doing a grouping or a min/max or averages. And, the two tables are not in an active relationship. Basically, I have a ProductID in each table. In TableA I have all the dates that each Product was purchased. In TableB I have the dates that they were sold. Each table is millions of rows and dozens of columns. What I am looking to do is to count how many days between the two dates.
