Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
gng
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.

 

Please help. Thanks!

1 ACCEPTED SOLUTION
clausm73
Helper III
Helper III

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.

View solution in original post

2 REPLIES 2
clausm73
Helper III
Helper III

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.

sokg
Solution Supplier
Solution Supplier

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.