Distinct count orders from customers 12 months after first order date

[ErikOmni]

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!

[Greg_Deckler · Accepted solution]

@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.

Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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.

[ErikOmni]

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