cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors