Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with date referencing between two different tables

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,

Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    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" )

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    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" )

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.