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
rrafferty37
Helper I
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):

 

OrderLine #Customer CodeDateSale
0011A1/1/201825
0012A1/1/201850
0013A1/1/201825
0014A1/1/201875

 

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
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

25 REPLIES 25
saragadamdines
Regular Visitor

There exist a  simpler solution for this problem using one filter.

First create a slicer for year using date column and then create a simle table where you count number of customer_ids and apply a advanced filter using sales as data column for greater than 'n'.

But if you are interested in making column for this problem then please go ahead with DAX query.

Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

 

How would you calculate this same measure for same period last year?

 

hi,

reuse this measure in a calculate Funktion with sameperiodlastyear Funktion at the end

@Power-CJ ,

Thank you - would that be in place of DATESBETWEEN In the 2nd half of the measure?

This doesn't seem to work:

michellem0771_0-1701978292137.png

 

No a new measure
 
New measure =
Calculate(
[old measure],
Sameperiodlastyear(Date)
)

@Power-CJ ,

 

I'm still getting an error; what am I doing wrong? I really appreciate your help.

michellem0771_0-1701980662736.png

 

@Power-CJ ,

I found it, there was an extra comma in the formula; it works fine now.  Thank you so much for your help!

Hello 

thanks for the solution.

I'm planing to use the measure for a card visual in order to Drill Through the KPI.

But the Dax Function Countrows doesnt support Drill Through.

How can i transform your solution to an Count whick makes Drill Through possible?

greets 

Hi,

Is the measure working fine in a non-drillthrough setup?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

yes there it works fine.

But using a card visual i cant implement Drill Through.

I had a same issue with a other measure.

There i was able to transfor the measure from a countrows to a sumx

after this the drill through was working

Hi,

I do not have experience with using drill throughs.  I would still like to try to help.  Share the download link of the PBI file.  In there, clearly show the problem and the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

ClientOrder Date
A04/21/2022
B04/12/2022
B03/17/2022
C04/28/2022
A04/29/2022
A04/27/2022
A04/24/2022
C04/22/2022
C04/28/2022

 

Thank you.

Hi,

You may download my PBI file from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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? 

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
https://www.linkedin.com/in/excelenthusiasts/

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!

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!

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.