Hi @Fowmy
Please check the measure below posted by @Anonymous , the problem I don't know how to call it, did you get the point?
// Assuming that there's:
// 1. a Customers dimension,
// 3. a Dates dimension (for the order dates),
// 4. an Orders fact table.
// The Orders fact (*-side) joins to Dates[Date]
// dimension (1-side) on Orders[OrderDate] and to
// Customers[CustomerID] on Orders[CustomerID].
// Orders must also have a column [Order No]
// which will number orders (within each customer)
// from 1 to N. All the ID columns in the fact table
// and dimensions will, of course, be hidden.
// The very last N available for a customer will,
// of course, be different among customers as one
// can't reasonably expect that all customers have
// placed the exact number of orders. Once this
// setup is in place, you can write a measure that will
// return the average number of days between [Order No] = n
// [Order No] = n+1 (all within the current context!).
[Avg No of Days To Next Order] =
// First, need to make sure that the column
// Orders[Order No] is filtered. If there's
// a filter on [Order No] = 1, then we'll be
// calculating the average num of days between
// the order numbers 1 and 2. If there's a filter
// on order number 10, the measure will return
// the avg number of days between order number
// 10 and 11 and so on...
if( hasonefilter( Orders[Order No] ),
var __orderNumber = selectedvalue( Orders[Order No] )
var __avg =
averagex(
Orders,
// For the current order find the next one
// (within the same customer AND THE CURRENT
// CONTEXT!) and caclulate the difference in
// the order dates.
var __currentOrderDate = Orders[OrderDate]
return
calculate(
// Get the next order for the same
// customer if there exists such an
// order. If not, BLANK will be returned
// and hence such a customer will not
// participate in the calculation of
// the average, as required. Please note
// that this calculation fully respects
// the current context, so based on the
// filters there might not be an (n+1)th
// order for the customer, even though it
// will exist in a modified (broader) context.
var __nextOrderDate =
CALCULATE(
SELECTEDVALUE( Orders[OrderDate], BLANK() ),
Orders[Order No] = __orderNumber + 1
)
return
DATEDIFF(
__currentOrderDate,
__nextOrderDate,
DAY
)
)
)
)
return
__avg
)
