Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
@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. ), "Column to add", <expression> )
Or, use SUMMARIZECOLUMNS()
SQLBI has an extensive article on this topic:
https://www.sqlbi.com/articles/introducing-summarizecolumns/
Thank you for sharing that @Anonymous.
@rrafferty37 , try this:
[# Customers above R12 Sales Threshold] = // Change this value as needed VAR Threshold = 5000 VAR Rolling12ByCustomer = ADDCOLUMNS( 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
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!!
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 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |