cancel
Showing results for
Did you mean:  Helper I

## Distinct count orders from customers 12 months after first order date

Hi!

Trying to distinct count how many customers have ordered 12 months from first order date. Have calculated the sales and it works fine. However, the order count returns incorrect numbers (see below example that should be 1) Formula looks like this:

12M Orders from Acqusition =

VAR __Customer = MAX(OrderData[CustomerID])
VAR __FirstOrderDate = MAX(OrderData[Customer.FirstOrder.1])
VAR __12MonthsEOM = EOMONTH(__FirstOrderDate,12)
VAR __12MonthsDate = DATE(YEAR(__12MonthsEOM),MONTH(__12MonthsEOM),DAY(__FirstOrderDate))
RETURN
SUMX(FILTER(ALL(OrderData),[CustomerID] = __Customer && [OrderDate] >= __FirstOrderDate && [OrderDate] <= __12MonthsDate),[#Orders])

#Orders is calculated like this:

#Orders = DISTINCTCOUNT(OrderData[OrderID])

Any ideas what might screw this up?

Thanks Erik!
1 ACCEPTED SOLUTION  Super User

@ErikOmni Not sure I completely follow but maybe something like:

``````12M Orders from Acqusition =
VAR __Customer = MAX(OrderData[CustomerID])
VAR __FirstOrderDate = MAX(OrderData[Customer.FirstOrder.1])
VAR __12MonthsEOM = EOMONTH(__FirstOrderDate,12)
VAR __12MonthsDate = DATE(YEAR(__12MonthsEOM),MONTH(__12MonthsEOM),DAY(__FirstOrderDate))
VAR __Table = FILTER(ALL(OrderData),[CustomerID] = __Customer && [OrderDate] >= __FirstOrderDate && [OrderDate] <= __12MonthsDate)
RETURN
COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"OrderID",[OrderID])))``````

Would be helpful to have sample data, etc.

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition  DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2  Super User

@ErikOmni Not sure I completely follow but maybe something like:

``````12M Orders from Acqusition =
VAR __Customer = MAX(OrderData[CustomerID])
VAR __FirstOrderDate = MAX(OrderData[Customer.FirstOrder.1])
VAR __12MonthsEOM = EOMONTH(__FirstOrderDate,12)
VAR __12MonthsDate = DATE(YEAR(__12MonthsEOM),MONTH(__12MonthsEOM),DAY(__FirstOrderDate))
VAR __Table = FILTER(ALL(OrderData),[CustomerID] = __Customer && [OrderDate] >= __FirstOrderDate && [OrderDate] <= __12MonthsDate)
RETURN
COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"OrderID",[OrderID])))``````

Would be helpful to have sample data, etc.

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition  DAX is easy, CALCULATE makes DAX hard...  Helper I

Thank you @Greg_Deckler ! Much appreciated. Will take note of being more clear in the future.   