Hi Heather,
The table ‘Prospect List Contact Lead’ contains two relevant fields, Contact.Status C and Lead (2).Status Lead. Each of these contains the current status of each prospect.
The tables ‘Contact History’ and ‘Lead History’ are log files of status changes. These tables show the old value and the new value with a created date for the change.
I created a relationship between the first table and the two history tables via the contact and lead ID fields.
The issue is that not every one of the prospects in the ‘Prospect List Contact Lead’ table have a status change in the history tables. What I need to do, which you already said, is figure out the status of each prospect at the end of each month. So if the status didn’t change, it just gets repeated as the same as current. I’m just really struggling with the DAX formula(s) to get there.
Any thoughts? Thanks! Sheila