Help with date referencing between two different tables

[Anonymous]

Hello,

I have a complicated ask that I was hoping to get some assistance on:

There are two tables, a Initiation Event table, and a Schedule table that I am using. Using the initiation event table, I need to take use the Effective Date to lookup against the Schedule table to determine which schedule Group to lookup and then the row record to lookup against, then once that is determined, use the Completed Date from the Initiation Event table, to determine if it is before or after a Locked Date column

Below is a link a sample PBIX file, along with a screenshot of the desired outcome. One of the schedule Groups does not have a Period Number, and will lookup against a different column than the other groups (see pbix for more information)

PBIX file = click here

Desired Outcome:

Any direction would be very helpful.

Thanks,

[Anonymous]

Hi @Anonymous ,

Does @DataInsights  's  method help you? If so, please mark his answer as a soultion to close the case. If not, please provide more details with your desired output.

How to Get Your Question Answered Quickly 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights · Accepted solution]

@Anonymous,

Try these calculated columns in table Initiation Event:

Period Number = 
VAR vGroup = 'Initiation Event'[Group]
VAR vEffDate = 'Initiation Event'[Effective Date]
VAR vTable =
    FILTER (
        Schedule,
        Schedule[Group] = vGroup
            && vEffDate >= Schedule[Period Start Date]
            && vEffDate <= Schedule[Period End Date]
    )
VAR vResult =
    IF ( NOT 'Initiation Event'[Group] = "D1", MAXX ( vTable, Schedule[Period Number] ) )
RETURN
    vResult

Period = 
VAR vGroup = 'Initiation Event'[Group]
VAR vEffDate = 'Initiation Event'[Effective Date]
VAR vTable =
    FILTER (
        Schedule,
        Schedule[Group] = vGroup
            && vEffDate >= Schedule[Period Start Date]
            && vEffDate <= Schedule[Period End Date]
    )
VAR vResult =
    MAXX ( vTable, Schedule[Period] )
RETURN
    vResult

Locked Date = 
VAR vGroup = 'Initiation Event'[Group]
VAR vPeriodNumber = 'Initiation Event'[Period Number]
VAR vPeriod = 'Initiation Event'[Period]
VAR vResult =
    SWITCH (
        vGroup,
        "D1",
            VAR vTableD1 =
                FILTER ( Schedule, Schedule[Group] = vGroup && Schedule[Period] = vPeriod )
            VAR vLockedDateD1 =
                MAXX ( vTableD1, Schedule[Locked Date] )
            RETURN
                vLockedDateD1,
        VAR vTableOther =
            FILTER (
                Schedule,
                Schedule[Group] = vGroup && Schedule[Period Number] = vPeriodNumber
            )
        VAR vLockedDateOther =
            MAXX ( vTableOther, Schedule[Locked Date] )
        RETURN
            vLockedDateOther
    )
RETURN
    vResult

Completed Before Locked Date = 
IF ( 'Initiation Event'[Completed Date] < 'Initiation Event'[Locked Date], "Yes", "No" )