cancel
Showing results for
Did you mean:
Helper I

## Count of Customers with greater than "n" sales on a rolling 12 month basis

Hello,

This is what my data table looks like (the pertitent part):

 Order Line # Customer Code Date Sale 001 1 A 1/1/2018 25 001 2 A 1/1/2018 50 001 3 A 1/1/2018 25 001 4 A 1/1/2018 75

This data is for a few thousand customers over the past 5+years.

What I am looking to do is get a count of the customers over a certain amount of sales in the last rolling 12 month period (for example \$5000 in the last 12 months).

Additionally, I have a year/month graph that shows the rolling 12 months over time. I would like to have the customer data shown the same way so that I can have a bar/line combo graph showing both metrics.

I am getting stuck on the count.

For the Rolling 12 Sales I have:

```R12 M Sales =
CALCULATE (
[Sales],
DATESINPERIOD ( 'Date'[Date],     EOMONTH ( MIN ( 'Date'[Date] ), 0 ),     -12, MONTH     ))```

And this works just fine.

Thanks!

1 ACCEPTED SOLUTION
Super User

Hi,

Create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of your Calendar Table.  In the Calendar Table, extract the Year and Month via these calculated column formulas

Year = Year(Calendar[Date])

Month = FORMAT(Calendar[Date],"mmmm")

To your visual, drag Year and Month from the Calendar Table.  Write this measure

Measure = COUNTROWS(FILTER(SUMMARIZE(CALCULATETABLE(VALUES(Data[Customer Code]),DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-11),MAX(Calendar[Date]))),[Customer Code],"ABCD",CALCULATE(SUM(Data[Sale]),DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-11),MAX(Calendar[Date])))),[ABCD]>5000))

This measure should give a count of customers every month who have in the 12 months ended that month given you business exceeding \$5000.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
14 REPLIES 14
Super User

Hi,

Create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of your Calendar Table.  In the Calendar Table, extract the Year and Month via these calculated column formulas

Year = Year(Calendar[Date])

Month = FORMAT(Calendar[Date],"mmmm")

To your visual, drag Year and Month from the Calendar Table.  Write this measure

Measure = COUNTROWS(FILTER(SUMMARIZE(CALCULATETABLE(VALUES(Data[Customer Code]),DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-11),MAX(Calendar[Date]))),[Customer Code],"ABCD",CALCULATE(SUM(Data[Sale]),DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-11),MAX(Calendar[Date])))),[ABCD]>5000))

This measure should give a count of customers every month who have in the 12 months ended that month given you business exceeding \$5000.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hi @Ashish_Mathur
What if we need to see for last week?

Super User

Hi,

Share some data, describe the question and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Thank you @Ashish_Mathur I need to calculate
1. The number of clients who ordered more than 1000 times last week

2.The total volume of orders (for those clients who ordered more than 1000 times)
3.The percent to total (The contribution of those clients order to the total volume.)

The expected result should be a scalar value and visualized on a scorecard.

My data table looks like

 Client Order Date A 04/21/2022 B 04/12/2022 B 03/17/2022 C 04/28/2022 A 04/29/2022 A 04/27/2022 A 04/24/2022 C 04/22/2022 C 04/28/2022

Thank you.

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Thank you @Ashish_Mathur
This is works great for last week. But I also have a date slicer and I want to give the flexiblity for my users to pick start and end date. How can I hard code the datesbetween to link with the date slicer?

Super User

That does not make a lot of sense to me.  last week will obviously be with respect to Today's date so how does a slicer come into play now?  Share some data to work with and clearly show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hello,

As things naturally progressed from using this solution, I have been trying to modify this to segment my customer base.

```COUNTROWS (
FILTER (
SUMMARIZE (
CALCULATETABLE (
VALUES ( Sales[CustomerCode] ),
DATESBETWEEN (
'Date'[Date],
EDATE ( MIN ( 'Date'[Date] ), -11 ),
MAX ( 'Date'[Date] )
)
),
Sales[CustomerCode],
"Metric", CALCULATE (
SUM ( Sales[Sales] ),
DATESBETWEEN (
'Date'[Date],
EDATE ( MIN ( 'Date'[Date] ), -11 ),
MAX ( 'Date'[Date] )
)
)
),
[Metric] > 2000
)
)```

Using the above code (thank you!) I replicated it a number of times to count how many customers are in various segments ([Metric] >500/1000/200/400/8000/10000, etc.)

I want to be able to see the value of sales within each of these segments.

I tried to SUM intially by summing on my sales column and passing the filter expression in the above code but the output was not what I was looking for.

If I understand the above, a summary table consiting of customer codes within a rolling 12 month period is created.  For each of these customer codes the sales over the same time period (written again within the sum expresioon inside the summarize expression) is calculated under the column name "Metric".  The countrows expression simply counts any row where Metric is greater than whatever is specified. I want to just sum the Metric column using the same parameters and seem to be hitting a wall.

Thanks!

Helper I

This function worked exactly as I needed it to! @Anonymous I will definitely be reading the article you linked to optimize this function. Thank you both so much, I have learned a great deal and have added a very useful component to my dashboard. Cheers!

Anonymous
Not applicable

@Ashish_Mathur , while this will likely still work, it is not optimal.

SUMMARIZE() has very poor performance when additional columns are added.  It's much better to use

```ADDCOLUMNS(
SUMMARIZE(
Table,
Column1,
etc.
),
)```

Or, use SUMMARIZECOLUMNS()

SQLBI has an extensive article on this topic:

https://www.sqlbi.com/articles/introducing-summarizecolumns/

Super User

Thank you for sharing that @Anonymous.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

@rrafferty37 , try this:

```[# Customers above R12 Sales Threshold] =
// Change this value as needed
VAR Threshold = 5000
VAR Rolling12ByCustomer =
VALUES(FactTable[Customer Code])
,"Rolling 12 Sales", [R12 M Sales]
)
VAR CustomersOverThreshold =
FILTER(
Rolling12ByCustomer
,[Rolling 12 Sales] >= Threshold
)
VAR Result =
COUNTROWS(CustomersOverThreshold)
RETURN
Result```

1) Build a temporary table that computes the Rolling 12 sales for each customer (the date in the current filter context will provide the proper rolling 12 months).

2) Filter that temporary table to only the those customers that have sales >= threshold (set as a variable so that you can adjust it, or call it from a measure)

3) Count the number of rows in the filtered table

Helper I

So this almost works.  For each particular year/month, if the customer had no sales in that given month they would not be counted (even though the R12 M Sales was over the threshold).

This was filtered on the most recent month completed (April 2019).  Still new to DAX, but it seems like at the month level is where the addcolumns expression starts, and if there are no sales for that month the customer is not added.  I am not sure how to remedy this, but I despite this not serving my exact needs I have learned a lot by going through this post line by line. Thank you!!

Super User

Are you just after a count of the distinct customer codes?

Would something like the following work:

```R12 M Customers =
CALCULATE (
DISTINCTCOUNT( Table1[CustomerCode] ),
DATESINPERIOD ( 'Date'[Date],
EOMONTH ( MIN ( 'Date'[Date] ), 0 ),
-12, MONTH
)
)```

Announcements