cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate Earliest Order Date and Subtracting from Create Date

Hello,

I have customers with their "individual" create dates in one table and "multiple" order dates in another table linked using customer id. I have to calculate the "earliest/first order" date and then subtract it from the "create date" for each indicvidual customer to get the number of days between.

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Calculate Earliest Order Date and Subtracting from Create Date

The simplest solution would be to add a calculated column to the 'Customers' table.

```DaysUntilFirstOrder =
VAR CustomerFirstOrderDate = CALCULATE( MIN('Orders'[Order Date]) )
RETURN
DATEDIFF( 'Customers'[Create Date]; CustomerFirstOrderDate; DAY )```

This formula assumes a relationship between 'Customers' and 'Orders' table.

The CALCULATE statement enforces that the first order date is found in the context of a single customers. Without CALCULATE the formula would find the first order for ANY customer.

Be aware that DATEDIFF can return an error if any order date found is less that the corresponding 'Customers'[Create Date]. Such an exception can be handled using IFERROR() - but this can have negative performance impact at processing time.

2 REPLIES 2
Established Member

## Re: Calculate Earliest Order Date and Subtracting from Create Date

Hi @gng

you have to create these measures

DateCreatedMeasure = MAX(Customer[DateCreated])

EarlierOrderDate = MIN(Orders[OrderDate])

and the result

Diff = VALUE([EarlierOrderDate]-[DateCreatedMeasure])

VALUE() is the poorly named 'convert to numeric type' function.

Frequent Visitor

## Re: Calculate Earliest Order Date and Subtracting from Create Date

The simplest solution would be to add a calculated column to the 'Customers' table.

```DaysUntilFirstOrder =
VAR CustomerFirstOrderDate = CALCULATE( MIN('Orders'[Order Date]) )
RETURN
DATEDIFF( 'Customers'[Create Date]; CustomerFirstOrderDate; DAY )```

This formula assumes a relationship between 'Customers' and 'Orders' table.

The CALCULATE statement enforces that the first order date is found in the context of a single customers. Without CALCULATE the formula would find the first order for ANY customer.

Be aware that DATEDIFF can return an error if any order date found is less that the corresponding 'Customers'[Create Date]. Such an exception can be handled using IFERROR() - but this can have negative performance impact at processing time.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,204)