Calculating data of different ID based on different start date

[reggieveggie]

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! 

[v-jianboli-msft · Accepted solution]

Hi @reggieveggie ,

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]

@reggieveggie 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.

      

Showcase Report – Contoso By SpartaBI

[reggieveggie]

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! 

[SpartaBI]

@reggieveggie please PM me with a link to zoom and teams and I will join