Look up using date range

[Anonymous]

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

[Anonymous]

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

[jtownsend21]

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]

@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]

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

[Anonymous]

@jtownsend21 

Why are date start and date end greyed out? Is it because it's the whole column instead of a set date? Same with employee number in the travel sheet.

It keeps giving me errors 

-Michael

[jtownsend21]

Yes, that is correct. I wasn't thinking about that. It needs some agregation, but  you can't use one. So that solution wont work. 

THe other solution is closer. I think you will need to use a filter expression, but not sure. I am actually heading out for the day, so you will have to post it to the forum, or I can take a look again on Monday. 

[Anonymous]

Don't worry about it Jtown I'm going to be out all next week. Going to California for vacation. (Much needed) While I will still work on it for the rest of the day, once it's over I am getting out of here!

-Michael

[MFelix]

Hi @Anonymous ,

Try adding the following two columns:

Job title =
CALCULATE (
    FIRSTNONBLANK ( 'Employee Dynamic'[Job Title]; 1 );
    FILTER (
        ALL ( Budget );
        Budget[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 ( Budget );
        Budget[EmployeNumber] = 'Travel 2019'[Employee]
            && 'Employee Dynamic'[Date Start] <= 'Travel 2019'[Date]
            && 'Employee Dynamic'[Date End] >= 'Travel 2019'[Date]
    )
)

Believe the naming of columns is correct should work as expected.

Regards,

MFelix

[Anonymous]

@MFelix 

I can understand most of the DAX (Even though I suck at formulating it myself) but I dont Really understand the circled part in this SS. It did return all errors for this one too unfortunately.

-Michael

[MFelix · Accepted solution]

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]

Yes, I still get an error and I'm not really sure why. Let me post some SS's

The 'Employee Dynamic'[Date Start] and 'Employee Dynamic'[Date End]. This is similar to Jtown's solution. 

This has been my WHOLE day lol

[MFelix]

Hi @Anonymous ,

In this case you problem is the dot comma syntax ";" replace it by comma "," it's a question of regional settings should work as expected.

Regards,

MFelix