Need Measure to show behavior AFTER date from column

[3Jk33f3]

I've searched the forum and haven't come across close to what I need.

We have a program internally that insepcts customer activity. There were some customers that received a letter 6 months into the program to remind them of the action that is expected of them.

I'd like to write a measure that shows their behavior AFTER the date they received the 6 month letter. Since these customers are on different schedules, they wouldn't receive the 6 month letter at the same time nor period. 

I've created some sample data. The data is collected via a PowerApp that feeds into a SharePoint list.  There are two lists, one which is the landing spot for the PowerApp data and another that is more of a reference and contains all the customer information such as name, address, how many containers they own, if they received a 6 month letter, and what date the letter was sent to them. The SharePoint lists are connected to a PBI report.

What I need: 

- to show the closure rate of their bins AFTER the 6 month letter date.

This will require the use of the PowerApp data SharePoint list and the Customer info SharePoint list (6 month letter date resides here).

I don't know where to begin with it. 

This is the sample input data (link below):

This is the customer data

Here's the sample data, I couldn't figure out how to attach the files to the post so here's a GoogleDocs link:

Customer reference:  https://docs.google.com/spreadsheets/d/1GvFydlNvx3nWVngYzFpHJuJ6CXTt7V60/edit?usp=drive_link&ouid=10...

Input data: https://docs.google.com/spreadsheets/d/1IqTXXBhF4pU55af_yS_OUh4jF46M2SHu/edit?usp=drive_link&ouid=10...

Your help is appreciated.

[lbendlin]

Links are asking for access.

[3Jk33f3]

@lbendlin Sorry about that... I've removed the restriction from the links. You should be able to access the files now. Thank you!

[lbendlin]

Who should we trust, the reference or the collected input? For Lake Cleaners the reference says two containers but only one was checked on that day.

How do you want to show the closure rate? As average of sums or average of averages?

What about the Craft Shop? It is missing from the reference table.

[3Jk33f3]

Hi @lbendlin ,

Great question. The reference sheet show what the business has, and the collected input are where the inspector documents their findings on their visit. For Lake Cleaners, they have two containers but it is possible that they only had one closed that day. That's the behavior we want to track. For us, closed bin lids equal less opportunity for contaminants to enter our water ways. I hope this makes sense. Ultimately, the behavior we want to see is that if Lake Cleaners has two bins then when the inspector does on their visit, the two bin lids are closed. I hope this makes sense.

[lbendlin]

Yes, it makes sense, but the inspector seemingly only found one bin. Did he/she ignore the other one? What would the close ratio be?

[3Jk33f3]

Sorry for the confusion. It is possible the inspector made an error when inputting the data. I've had to modify the Powerapp to only take in the number of lid bins that business owns to avoid that error from the inspector.

[lbendlin]

No need to apologize - i just want to understand what rules to apply in such scenarios.  Should the non-inspected container count as open or closed?

[3Jk33f3]

If it not marked at closed, it would be assumed to be open. 

[lbendlin]

Your sample data is inconclusive. None of the business that received the 6 month letter have been inspected after that.

[3Jk33f3]

@lbendlin I tried your formulas with my actual data set and I achieve the same results as you. The formula is not registering a difference between a business that did receive a 6 month letter and one that did not. It's also not registering all the businesses that did receive a 6 month letter. Could it be that it's because it's a SharePoint date column?

[lbendlin]

not really.  See if you can produce sample data that would cover all possible scenarios.

[3Jk33f3]

@lbendlin Ok, I redid the data sets without modifying them. I just deleted the location information. This is the truest form of the data. 

Reference: Business Reference 

Input: Input 

[lbendlin · Accepted solution]

I applied exactly the same measures - please check.

[3Jk33f3]

@lbendlin Yes, Perfect! Thank you so much for your patience and assistance!

[3Jk33f3]

It was a lot of data with sensitive information, I tried my best to turn it into sample data. Thank you for attempting. I will take a look at the PBIX and your formulas and see if I can apply to the actual data sets. Thank you again, I really appreciate your time and effort.