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 budget group they are in. ( see attached SS's)  But at the same time I have to use the date range to verify that the expense is being charged to the right budget group. Currently there is only one employee that has changed budget groups, highlighted in the SS's below. Thank you for the help

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

12 Replies

  • 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