Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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.
 
screen.png
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). 
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.