Forum Discussion
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...
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
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
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
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