topic Re: Average Days to Order by Year and Product with Customer Count in DAX Commands and Tips

Average Days to Order by Year and Product with Customer Count

etane — Wed, 02 Jul 2025 15:07:21 GMT

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!

Re: Average Days to Order by Year and Product with Customer Count

lbendlin — Wed, 02 Jul 2025 00:04:50 GMT

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

Re: Average Days to Order by Year and Product with Customer Count

etane — Wed, 02 Jul 2025 00:19:35 GMT

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

Re: Average Days to Order by Year and Product with Customer Count

lbendlin — Wed, 02 Jul 2025 00:31:30 GMT

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

Re: Average Days to Order by Year and Product with Customer Count

etane — Wed, 02 Jul 2025 03:30:45 GMT

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.

Re: Average Days to Order by Year and Product with Customer Count

v-dineshya — Wed, 02 Jul 2025 08:48:56 GMT

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

Re: Average Days to Order by Year and Product with Customer Count

etane — Wed, 02 Jul 2025 16:21:15 GMT

Thanks. This is exactly what I am looking for!