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.

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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.

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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 T-Shirt Design Challenge 2023

Submit your creative T-shirt design ideas starting March 7 through March 21, 2023.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors