Forum Discussion
Calculating data of different ID based on different start date
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!
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.
4 Replies
v-jianboli-msftCommunity Support
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.
- SpartaBI
Community Champion
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.
In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.
SpartaBI Logo
Visit SpartaBI website Visit SpartaBI Linkdin Visit SpartaBI Facebook
Showcase Report – Contoso By SpartaBI
