Formula in Power Pivot

Problem - I need to identify the Program for where the medication was consumed. I can get the program for every instance except when someone has left a program partway through the month. I will need it to then default to the 'active' Program.
tblProgramsWithIndividuals - This identifies the Name of the person, the program and the EnrollmentDate and DischargeDate (if there is one) for each. If there is no DischargeDate, they are still active in that program.
tblMARPasses - This table identifies the Name of the Person, the medication, the date and time and if the medication was administered or other status.

By the attached file you can see I am able to get this to work in the table - tblMARPasses, Column Q - TiePassProgName. Although it does cause a circular reference. Idealy, Columns I - Q will only exist in the Power Pivot. But I can not figure out the syntax to make the TiePassProgName default to whatever program is active ... if ProgramLookup is blank.

Here are descriptions of the columns in tblMARPasses...
PassID : is just an index columns
Mon-Fri : Identify if the date of the medication pass was on a weekend.
Daytime : Identify if it was during Daytime Program hours
Include : Identify if the pass was during the week and during DayProgram hours then Include it in the DayProgram count.
CouldbeDay : Create index to identify if the pass was for a Day program or Res (residential).
TiePassName : This is still trying to make an index. If there is an error in CouldbeDay, default to the Name-Res (residential).
ProgramLookup : Using TiePassName linked to TiePassName in tblProgramsWithIndividuals - check that the date the medication was given...falls within the enrollment and discharge dates of the program.
StillActive : If there is an error in ProgramLookup...give me whatever program they are active in.
TiePassProgName: should be the person and the Program they were attending at the time the medication was given.

I am open to changing any or all of the columns from PassID to TiePassProgName. All of the others are system generated and will be loaded into PowerPivot by powerquery. The actual tblMARPasses is usually around 90,000 records on a monthly basis so I do not want to load it into the actual excel spreadsheet...I did in this example to hopefully show what I need my outcome to be.

Thank you very much for any possible help.


rays97

Make you file smaller, there is no need of so much data.

Unfortunately, that is as small as I can get it unless you can tell me how?

The original file with actual data becomes 2.5G.