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

jtownsend21's avatar
jtownsend21
Responsive Resident
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    MFelix
    Super User
    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

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    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

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    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's avatar
    Anonymous
    Not applicable
    7 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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