Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

lookup nearest date

Table 1 Name  Date in Service First Project Second Project Alex K 1-1-2018 I want here the first project  of Table 2 of Alex K based on project start date vs Date In service Here  the ...
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
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Community 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
    Community 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?

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        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
)