create a measurement to factor two different time from two dimensions

[coloful_architect]

Hi community,

I need some help to create a measurement at bat chart or KPI to show sales number which take consideration of two different time from two different dimensions.

I have attached my sample data with three tabs. My loaded data structure is: 

Major sheet links to product flag  sheet by Product key and also links to Customer flag sheet by Customer number

Product Flag sheet has a number of N products with particular effective date (N Product Effective Date )

Customer Flag sheet has a number of N customers with particular effective date (N Product Effective Date)

so my purpose is to only show those customer or product 's numbers ONLY since their effective date.

eg, if one product 's effective date is May 22 but its associated customer is only effective by June 22. 

I only want to show all  companies 'sales for this product after June 22...any sales prior to June 22 , I need to exclude.

same logic if this product effective date is May 22 but the customer started a bit earlier like Jan 22. I only count sales since May 22.

The key at major sheet is a concatenation between product key and month number to enable me to make set expression.

The measurement I got so far for my bar chart and KPI is this 

Sum({$<Key= {"=Month>=Date(RangeMax(Max([N Product Effective Date]),Max([Customer N Effective Date])),'MMM-YY')"},[Customer N Flag]={'YES'},[N Product Flag]={'YES'}>}[sales]

what I have here is to only distill out those sales at latest date either for N product or N customer

However, for some reasons , the number does not seem correct. And I put into a plain table, some of customers works and some of them do not. 

wondering if anything I miss.  and it is for a bar chart and KPI...

Much appreciated your help

[coloful_architect]

Attached the my sample data

[coloful_architect]

a simplified walk-thru of my problem in case there is a quick solution.

down below is the data structure of my attached excel file.  ( that novakey at the major sheet is product key)

sales is from major sheet.  but filters for customer and product are from two other sheets.

Among all product and customer , there are some special products and customers with effective dates.

Want to only show their sales since their different effective dates.

I have made this set expression . however it only works for some of them...not all of them...not sure what I miss

Sum({$<Key= {"=Month>=Date(RangeMax(Max([N Product Effective Date]),Max([Customer N Effective Date])),'MMM-YY')"},[Customer N Flag]={'YES'},[N Product Flag]={'YES'}>}[sales]

[ckarras22]

Hello,

Please check your subset ratios on your keys. It seems that you miss ids that are present in Major Sheet but not in Flag tables 

[coloful_architect]

Hi Ckarras22..

that novakey at major sheet is the product key to link flag table.