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
User | Count |
---|---|
77 | |
76 | |
41 | |
30 | |
24 |
User | Count |
---|---|
96 | |
91 | |
53 | |
47 | |
46 |