Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Look up using date range

I know this has been covered a bunch of times. But I can't seem to understand exactly how to set it up. Essentially I have an expense report, and I want to do a look up by employee number to see what...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    My error forget to change all the names that I used in my example this should do the trick:

     

    Job title =
CALCULATE (
    FIRSTNONBLANK ( 'Employee Dynamic'[Job Title]; 1 );
    FILTER (
        ALL (  'Employee Dynamic' );
         'Employee Dynamic'[EmployeNumber] = 'Travel 2019'[Employee]
            && 'Employee Dynamic'[Date Start] <= 'Travel 2019'[Date]
            && 'Employee Dynamic'[Date End] >= 'Travel 2019'[Date]
    )
)


Budget Group = 
CALCULATE (
    FIRSTNONBLANK ( 'Employee Dynamic'[Budget Group]; 1 );
    FILTER (
        ALL (  'Employee Dynamic' );
         'Employee Dynamic'[EmployeNumber] = 'Travel 2019'[Employee]
            && 'Employee Dynamic'[Date Start] <= 'Travel 2019'[Date]
            && 'Employee Dynamic'[Date End] >= 'Travel 2019'[Date]
    )
)

    Regards,

    MFelix

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Bump, I know it seems like such a simple solution, I just can't figure it out

  • jtownsend21's avatar
    jtownsend21
    Responsive Resident
    7 years ago

    Sorry I am just seeing this. I would have gotten back sooner if I had seen it. I don't know how simple it is since your date column in the Expense Table wont match the start or end date in the Employee Table. 

    I am not sure if this will work, but this is my first guess (you will have to adjust the table names. I just guessed on that part).

    Budget Group LookUp = 
CALCULATE(
    LOOKUPVALUE(
        'EmployeeTable'[Budget Group],
        'EmployeeTable'[EmployeeNumber],
        'ExpenseTable'[Employee Number]
    ),
    DATESBETWEEN(
        'ExpenseTable'[Date],
        'EmployeeTable'[Date Start],
        'EmployeeTable'[Date End]
    )
)
    Job Title LookUp = 
CALCULATE(
    LOOKUPVALUE(
        'EmployeeTable'[Job Title],
        'EmployeeTable'[EmployeeNumber],
        'ExpenseTable'[Employee Number]
    ),
    DATESBETWEEN(
        'ExpenseTable'[Date],
        'EmployeeTable'[Date Start],
        'EmployeeTable'[Date End]
    )
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      jtownsend21 

      No worries. I did something similar to this but couldnt figure it out. I just replicated yours and it gave me an error shown here. Thanks Jtown

      • jtownsend21's avatar
        jtownsend21
        Responsive Resident
        7 years ago

        I was afraid that would happen. Basically it's a many to many relatinoship and it can't figure out what to do. 

        Try this. 

        IF(
    DATESBETWEEN(
        'ExpenseTable'[Date],
        'EmployeeTable'[Date Start],
        'EmployeeTable'[Date End]
    ),
    CALCULATE(
        LOOKUPVALUE(
            'EmployeeTable'[Job Title],
            'EmployeeTable'[EmployeeNumber],
            'ExpenseTable'[Employee Number]
        )
    )
)