Forum Discussion
PBI_Member_01
Helper III
Helper IIICalculate Average Days for First Order (For All Customers)
Hi everyone, I am having troubles creating this measure in Power BI. The dataset I am connected to is DirectQuery. Please see image below for reference. I want to create a measure which a...
Ok I hope this will work
Days To First Order = AVERAGEX ( VALUES ( CUSTOMER[Cust ID] ), VAR DateCreated = CALCULATE ( MAX ( CUSTOMER[Cust Date Created] ) ) VAR FirstOrderDate = CALCULATE ( MIN ( ORDERS[Order Date] ), ORDERS[Order Type] IN { "A", "B" } ) RETURN DATEDIFF ( DateCreated, FirstOrderDate, DAY ) )
tamerj1
Community Champion
Community Championplease try
Days To First Order =
VAR DateCreated =
MAX ( CUSTOMER[Cust Date Created] )
RETURN
AVERAGEX (
VALUES ( CUSTOMER[Cust ID] ),
VAR FirstOrderDate =
CALCULATE (
MIN ( ORDERS[Order Date] ),
ALLEXCEPT ( CUSTOMER, CUSTOMER[Cust ID] ),
ALL ( ORDERS ),
ORDERS[Order Type] IN { "A", "B" }
)
RETURN
DATEDIFF ( DateCreated, FirstOrderDate, DAY )
)
PBI_Member_01Helper III
Helper IIIHi tamerj1 ,
I tried the above solution but now it is going completely off even on individual customer level. Please have a look at image below:
So as opposed to the correct values of 21, 12, 24, 2, and 1 which I was getting before by using your calculation, now even those values are off.
Can you please take a look at this again?
Highly appreciate you putting your time and effort on this.
Thanks and Regards
- tamerj1
ok, let's go 2 steps backwards and try the following. Let's see how it behaves.
Days To First Order = VAR DateCreated = MAX ( CUSTOMER[Cust Date Created] ) RETURN AVERAGEX ( VALUES ( CUSTOMER[Cust ID] ), VAR FirstOrderDate = CALCULATE ( MIN ( ORDERS[Order Date] ), ORDERS[Order Type] IN { "A", "B" } ) RETURN DATEDIFF ( DateCreated, FirstOrderDate, DAY ) )- PBI_Member_01
Hi tamerj1 ,
Okay so now the individual numbers are back to correct values, but the average still seems to be an issue. Please see the image below:
Thank you once again for your input on this.
Kind Regards- tamerj1
Ok I hope this will work
Days To First Order = AVERAGEX ( VALUES ( CUSTOMER[Cust ID] ), VAR DateCreated = CALCULATE ( MAX ( CUSTOMER[Cust Date Created] ) ) VAR FirstOrderDate = CALCULATE ( MIN ( ORDERS[Order Date] ), ORDERS[Order Type] IN { "A", "B" } ) RETURN DATEDIFF ( DateCreated, FirstOrderDate, DAY ) )