Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi All,
I'm trying to create a measure that will help me sum up the frequency of orders by customers starting from their respective start dates.
The data are separated into two tables, I have created a many-to-one relationship from 'order date'[Customer ID] to 'register date'[Customer ID].
The outcome of the measure should be able to reflect the total order as shown in the picture.
Please help! Thank you!
Solved! Go to Solution.
Hi @Anonymous ,
Please try:
Count_Date =
var _a = CALCULATE(DISTINCTCOUNT('Table (2)'[Order Date]))+0
var _b = CALCULATE(DISTINCTCOUNT('Table (2)'[Order Date]),FILTER('Table (2)',[Order Date]>=MAX('Table'[Register Date])))
return IF(ISINSCOPE('Table (2)'[Order Date]),_a,_b)
Use the matrix visual:
Then change the format of the matrix
Turn off the Row subtotals:
Change the name of Column subtotals:
Turn off the step layout:
Final Output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please try:
Count_Date =
var _a = CALCULATE(DISTINCTCOUNT('Table (2)'[Order Date]))+0
var _b = CALCULATE(DISTINCTCOUNT('Table (2)'[Order Date]),FILTER('Table (2)',[Order Date]>=MAX('Table'[Register Date])))
return IF(ISINSCOPE('Table (2)'[Order Date]),_a,_b)
Use the matrix visual:
Then change the format of the matrix
Turn off the Row subtotals:
Change the name of Column subtotals:
Turn off the step layout:
Final Output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous you need to create a 1:m between Customer ID columns (first delete the relationship between the dates you created), and just create a simple measure like COUNTROWS('Orders Table Name') and put in a matrix next to the whatever columns you need from the customer table.
Hi @SpartaBI ,
COUNTROWS will not work as I only need the total number of order after the customer's registration date. Is there any other ways to solve this? Thanks!
@Anonymous please PM me with a link to zoom and teams and I will join
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
72 | |
38 | |
31 | |
26 |