Help with dynamic lists being 'locked down'

[Izzii0x]

Hi all,

I am producing a report which contains several sources of data relating to marketing and applications. From this, I will be able to pinpoint which salesperson converted the application, as I can check back into their call trackers and pull the most recent date of contact before application.

This is all fine, but my concern is that the data is linked via the company registration number, not the application, and the following year the customer may apply again but unrelated to any marketing - so it shouldn't count as a conversion the second year for that same salesperson. From a 'conversion report' point of view, this is easy, I just ensure the 'contact date' is within X months and therefore it will show as no direct conversion. However, my issue is that the salespeople still want to see the dynamic list of who converted who in the previous year.

Is there a way I can almost 'lock down' the data into a list or produce a query that will maintain who converted the application in year 1 and keep it separate to who converted the same company for a new application in year 2?

Hopefully this makes some sense, but I unfortunatelty don't have the data to attach at this point as it is still a concept. The end result that I am after is 1 report which shows the applications in the last 12 months and who converted them and another that shows an ongoing list of clients and who converted each application per year (which does not auto update previous records once the same company is converted by someone else).

Any help would be much appreciated! Thank you

[lbendlin]

You may not want to do anything this long term in Power BI. See if you  can implement your logic in the data source.

In any case - when you are ready to share sample data we can have a look.

[Izzii0x]

Technically, this is Excel but Power Query all the same, it will be used in PBI as well.

I've made a sample (It is a similar concept but in reality the trackers and applications all come from different sources.) but I can't see anywhere to attach it? Do I have to have been an active member for a certain amount of time?

Not sure about implementing the logic in the data source as they are all separate dynamic lists of calls or applications etc. Although, I was wondering if I could do 3 merges, latest calls and applications in the past rolling year, the rolling year before that and again before that- and then append them all afterwards so we have the last 3 years separately?

It does let me add photos, so I can try and explain my sample set a bit without attaching it. The data on the trackers holds each call made and has a unique company reg # per company and the date of the call, the applications also has the date of application and that company reg#, so the issue I am having is that if someone calls company X in 2020 and converts them, and then someone else calls them in 2021 and converts them, it changes the 2020 conversion. In reality, these applications could come in twice in a year or within different months of new years, so it is too simple for me to sort them by year.

All 3 applications for company 888 are showing as Tester3

Tester 3 called them in 2021 only

Tester 2 called them in 2020 but this is lost due to the 2021 call by Tester2