Forum Discussion

3Jk33f3's avatar
3Jk33f3
Icon for Helper I rankHelper I
2 years ago
Solved

Need Measure to show behavior AFTER date from column

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=103619957917641337701&rtpof=true&sd=true

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

 

Your help is appreciated.

 

 

 

15 Replies

    • 3Jk33f3's avatar
      3Jk33f3
      Icon for Helper I rankHelper I

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

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User

        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.