Average Days to Order by Year and Product with Customer Count

[etane]

Hello.

I am trying to determine:

X) Narrow down customer base to those who made first purchase of Product B in 2023 and StageName is Completion
A) Number of customers in X

B) Number of customers in X who has 5 or more units ordered

C) Which customers are in B?  (Recreating this table but without leveraging the visual filter in red circle as I will have other measures in the same table)

D) # of customers with at least 5 orders with first order in 2023 / # of customers with first order in 2023 (C divided by B)

E) Average days it takes B to get from 1st to 5th order 

Here's my WIP file:  Link

Thanks!

[v-dineshya · Accepted solution]

Hi @etane ,

Thank you for reaching out to the Microsoft Community Forum.

You want to analyze Product B purchases at StageName is "Completion" for customers who Started in 2023, who has more than 5 orders and some other conditions.

Please refer below steps.

1. Created DAX measure for, Customers who started in 2023.

2. Created DAX measure for, No of customers in A who have more than 5 orders , Product B completion orders.

3. Created Calculated table, Customers who started in 2023 and reached 5 orders.

4.  Created DAX measure, for Conversion rate to more than 5 orders.

5.   Created DAX measure,  For Average days it takes B to get from 1st to 5th order.

Note: I have created DAX measures in "Sales Table"and created separate Calculated table "Qualified Customers Table" in attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[etane]

Thanks.  This is exactly what I am looking for!

[lbendlin]

Please be more specific.  You want this by year, or overall?

[etane]

Sorry I wasn't clear in the requirements.  I restated the requirements hopefully they are clearer now. 

[lbendlin]

You can build on the sample measure I showed It will react to your other filters.  Then you can use that measure as a visual filter.

2 at least five and started 2023 = 
var a = CALCULATETABLE(ALLSELECTED('Sales Table'[Customer]),'Z - Calendar'[Year#]=2023)
var b = SUMMARIZECOLUMNS('Sales Table'[Customer],filter('Sales Table',[Customer] in a),"ct",CALCULATE(DISTINCTCOUNT('Sales Table'[Order Number])))
return countrows(filter(b,[ct]>4))

For item E) you would use the TOPN(5) orders per customer within the filter context and then subtract the minimum order date from the maximum order date

[etane]

The Atleast 5 and started in 2023 dax doesn't seem to work in my test file:

It seems to be counting customers with less than 5 cases.  Probably because the Product and Stage filter isn't in the dax.

And, I want to avoid using visual filters as I want to be able to use multiple measures in the same table such as seeing YOY new users, users with over 5, and how fast to 5 cases.