cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
basroozen Member
Member

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
ThomasFoster Established Member
Established Member

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

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.
 
screen.png
basroozen Member
Member

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

Thank you for the quick reply @ThomasFoster!

 

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). 
Capture.PNG

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

 

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 146 members 1,555 guests
Please welcome our newest community members: