Forum Discussion

gng's avatar
gng
Frequent Visitor
9 years ago
Solved

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...
Need Help
  • clausm73's avatar
    clausm73
    9 years ago

    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.

clausm73's avatar
clausm73
Icon for Advocate II rankAdvocate II
9 years ago

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.