Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |