Difference between dates by earliest date

[JamieLee]

Hi everyone,

Could someone please help me with this:

I’m creating a report in which I would like to show the difference in days between the date a product has become available for customers and the date of the first order of that product.

In Table ‘Products’, all products are listed once. I would like to add a column to this table called ‘DaysTillOrder’.

In Table ‘Orders’, all orders are listed. A product can be ordered multiple times.

The tables are linked by ProductID.

I’ve tried formulas to calculate the difference between dates. What I can’t figure out though, is how to calculate with the first Order date of a product only.

I’ve also tried to create a column with the minimum order date per product number as to calculate the difference from there. I ended up with the same minimum order date for every product, so something goes wrong there.

So I’m looking for the difference between CreationDate and earliest Orderdate per ProductID in days.

The structure looks like this:

Products
ProductID	CreationDate
001	01/01/2018
002	01/01/2018
003	13/04/2018
004	07/06/2018

Orders
OrderID	ProductID	OrderDate
021	001	03/01/2018
022	004	05/07/2018
023	001	02/08/2018
024	001	25/08/2018

Hope someone can help to find a good way to do this. It’s much appreciated!

[v-yuta-msft]

Hi JamieLee,

Could you show your expected result or clarify more details about your requirement?

Regards,

Jimmy Tao

[JamieLee]

Hi Jimmy,

Thanks for your reaction. The expected result would look like the 3rd column 'DaysTillOrder' in the Products table:

Products
ProductID	CreationDate	DaysTillOrder
001	01/01/2018	2
002	01/01/2018	blank
003	13/04/2018	blank
004	07/06/2018	31

Orders
OrderID	ProductID	OrderDate
021	001	03/01/2018
022	004	05/07/2018
023	001	02/08/2018
024	001	25/08/2018

Product 001, was ordered several times as we can see in the Order table, but the earliest order date was 03/01/2018, so only two days after the CreationDate in the Products table. This is why DaysTillOrder = 2.

Product 002 and 003 have not been ordered, so there is no data to retrieve from the Order table. This returns a blank (or a value like 'not applicable').

Product 004 was ordered slightly over a month after its creation date and is thus returning the value of 31 days.

Hope this clarifies?

Kind regards,

JamieLee