cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Number of customers who placed at least X orders over the last X months

Hi,

I’ve been struggling with this issue for the last couple of hours. Perhaps you can help me out with it.

I have an ORDER table with order dates and customer ID’s, this is a simplified version of that table:

 Order date Customer ID Jan 1 2018 AA1 Jan 2 2018 AA1 Jan 3 2018 BB2 Feb 1 2018 AA1 Feb 1 2018 CC3 Feb 1 2018 DD3 Feb 1 2018 AA1

I also have a separate date table that is marked as a date table, and which is linked to this ORDER table.

I’ve tried creating the following four measures:

1. Number of customers who placed 2 or more orders in this month – Don’t know how to do this. I’ve tried doing a distinctcount where the orderdate count is higher than 1, but that won’t work.
February result would be 2.
2. Number of customers who placed at least 1 order this month – FIXED - this one is just a distinctcount of the amount of customer ID’s.
February result would be 3
3. Number of customers who placed at least 1 order over the last 3 months – I’ve tried doing this with the DATEADD function but it will still only return the results of the current month
February result would be 4
4. Number of customers who placed no orders or last placed their order more than 3 months ago – FIXED – this will be the leftovers.
February result would be 0

What I’m struggling with is that I want to show these numbers in a report, and also want to show the Month over month increase/decrease for each of these segments. Any help is greatly appreciated!

Thanks

Bas

2 REPLIES 2
Anonymous
Not applicable

I loaded your example data as table1 into a pbix. The following measures give what you want

`Distinct Customer Count = DISTINCTCOUNT ( Table1[Customer ID] )`
```Two Plus Orders =
CALCULATE (
[Distinct Customer Count],
FILTER ( Table1, CALCULATE ( COUNTROWS ( Table1 ) ) >= 2 )
)```
```At least one order in L3M =
CALCULATE (
[Distinct Customer Count],
DATESINPERIOD (
Table1[Order Date].[Date],
ENDOFMONTH ( Table1[Order Date].[Date] ),
-3,
MONTH
)
)```
I have used the inbuilt date table for ease, however if possible you should use a seperate date table and use that instead of the .[Date] notation.

Anonymous
Not applicable

Thank you for the quick reply @Anonymous!

Both formula's don't seem to work for me. But as they work perfectly from your screenshot I think this might has something to do with the rest of my model.

The two plus orders (segment 1) returns blanks, and the At Least One Order in L3M (segment 3) returns the same results as the distinctcount formula (segment 2).

For Segment 1 I've used:

```Segment 1 = CALCULATE(
CALCULATE(
DISTINCTCOUNT('Sales Data'[Customer ID]),
FILTER('Sales Data',CALCULATE(COUNTROWS('Sales Data') >= 2))
)```

I've tried the same calculation by doing a sum of the amount of orders, but it won't work either. I've checked the data in Excel and there are plenty of customers who have ordered more than twice every month. Could this have anything to do with my date filters in my report? I don't have any page or report filters actived.

Segment 2 is just a distintcount

`CALCULATE(DISTINCTCOUNT('Sales Data'[Customer ID]))`

For Segment 3 I now have:

```CALCULATE(
DISTINCTCOUNT('Sales Data'[Customer ID]),
DATESINPERIOD (
_Date[Date],
ENDOFMONTH (_Date[Date]),-3,MONTH)
)```

_Date is my date table.

I don't understand what the .[date] part in your formula does, when I add it here things get broken. How should I interpret that?

Thanks!
Bas

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors