Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

lookup nearest date

Table 1
Name Date in ServiceFirst ProjectSecond Project
Alex K1-1-2018I want here the first project  of Table 2 of Alex K based on project start date vs Date In serviceHere  the second
Jay kay1-2-2018  
Bernd leo1-3-2018  
    
Table 2
Name name ProjectProject Start DateProject End Date
Alex KAA technology2-1-201814-2-2018
Alex KAB technology15-2-201811-3-2018
Alex KAC technology12-3-201814-2-2018
Jay kayAA technology15-2-201819-2-2018
Jay kayAB technology20-2-201811-3-2018
Jay kayAC technology12-3-201814-4-2018
Jay kayAD technology15-4-20181-3-2018
Bernd leoAA technology2-3-201811-3-2018
Bernd leoAB technology12-3-201814-3-2018
Bernd leoAC technology15-3-2018 

 

i have 2 tables and in table one i want to  have the first and scond project of table 2 based on the nearest Project start date in comparison with the date in service

example: Alex K first project is AA technology because 2-1-2018 is the first nearest date tot the date in service 1-1-2018

 

kind regards

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Thank so much! 

    I also need the start date of the first and second project.

    Can you help me with it?

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    Try with these.

     

    Date First Project =
CALCULATE (
    MIN ( Table2[Project Start Date] ),
    FILTER (
        CALCULATETABLE ( VALUES ( Table2 ) ),
        Table2[Project Start Date] >= Table1[Date in Service]
    )
)
    Date Second Project =
CALCULATE (
    MIN ( Table2[Project Start Date] ),
    FILTER (
        CALCULATETABLE ( VALUES ( Table2 ) ),
        Table2[Project Start Date] >= Table1[Date in Service]
    ),
    Table2[name Project] <> FirstProject
)

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Anonymous

     

    Try these calculated columns

     

    First Project =
VAR mydate =
    CALCULATE (
        MIN ( Table2[Project Start Date] ),
        FILTER (
            CALCULATETABLE ( VALUES ( Table2 ) ),
            Table2[Project Start Date] >= Table1[Date in Service]
        )
    )
RETURN
    CALCULATE (
        MIN ( Table2[name Project] ),
        FILTER (
            CALCULATETABLE ( VALUES ( Table2 ) ),
            Table2[Project Start Date] = mydate
        )
    )
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Anonymous

       

      Second Project =
VAR FirstProject = Table1[First Project]
VAR mydate =
    CALCULATE (
        MIN ( Table2[Project Start Date] ),
        FILTER (
            CALCULATETABLE ( VALUES ( Table2 ) ),
            Table2[Project Start Date] >= Table1[Date in Service]
        ),
        Table2[name Project] <> FirstProject
    )
RETURN
    CALCULATE ( MAX ( Table2[name Project] ), Table2[Project Start Date] = mydate )
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Thank so much! 

        I also need the start date of the first and second project.

        Can you help me with it?