Count of items on one table where the date is between two dates on another table

[Anonymous]

Hey All,

Basically, I have two tables: Case, and opportunity. These are linked through another table, Customer.

Basically, I wanted to find out how many cases there are within one year previous of the opportunities close date, for each opportunity. Using a column is too much memory because of the size of the objects, so I wanted to use a measure.

This is how I calculated it, but it doesn't seem to be working:

Cases Yr before Close = Calculate(DistinctCount('SF Case'[id]), Filter(ALL('SF Case'[id]), MAX('SF Case'[createddate]) > MAX('SF Opportunity Dataset'[Prev year close]) && MAX('SF Case'[createddate]) < MAX('SF opportunity Dataset'[Closedate])))

where 'sf case'[id] is the unque case identifier, case created date is when it was created, the prev year close is the closed date -1 year, and the closed date is the closed date. 

for the visuals, I am using the scatter plot with the opp id as the unique identifier.

If anyone has ideas, let me know!

[amitchandak]

@Anonymous , Try like

Cases Yr before Close = Calculate(DistinctCount('SF Case'[id]), Filter(all('SF Case'[id]), ('SF Case'[createddate]) > MIN('SF Opportunity Dataset'[Prev year close]) && ('SF Case'[createddate]) < MAX('SF opportunity Dataset'[Closedate])),values('SF Case'[id]))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Says I can't attach the powerbi file, so I'll screenshot:

Link between the two tables through the "account id" - an account can have many opps and many cases, but each account id is distinct, which allows the link

Opportunity dataset - basically want to count the SF cases created between those two dates. These are linked through the account object field "accountid"

case data, in which I want to count the ids (which represent case) where the date is between the two dates on the opportunity object 

The output would ultimately be this;

where each opportunity ID is listed, and the count of linked cases between the two dates on the opportunity table is calculated. I'd probably put this on a scatter plot used to compare against other variables

Thanks

[Anonymous]

Measure still doesn't "see" created dates here without the aggregate. I'll create something to use and publish it here

[amitchandak]

@Anonymous , Not very clear, Try like

Cases Yr before Close = Calculate(DistinctCount('SF Case'[id]), Filter(values('SF Case'[id]), ('SF Case'[createddate]) > MIN('SF Opportunity Dataset'[Prev year close]) && ('SF Case'[createddate]) < MAX('SF opportunity Dataset'[Closedate])),all('SF Case'[id]))

Very similar to Current Employee - https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

To clarify, I'm looking to build a scatter plot that shows each opportunity, with the x axis being # of cases (based on their created date) being within 1 year previous of the opportunities close date. The Y axis is based on a metric on the account object, so not relevant here.

[Anonymous]

Hey! Thanks for the response. Because I'm using a measure, the case created date isn't working because its looking for an aggregate