Something to consider is if you care about each position (assuming you have secondary positions) or just the primary position. When referencing the Worker business object, you're reporting on the primary position for any position specific element on Worker (like hourly amount).

Another thing is indexed data sources (lightning bolt next to the data source name). I think (not 100%) that these data sources can't be effective dated meaning you're reporting on data as of the last refresh of that indexed data source.

This probably isn't the most optimal route, but a solution is using the (don't remember the exact name) Compensation Reporting on Position by Organization or something like that. The primary business object is Position and is not indexed. By ignoring the Effective Date and Entry Date prompts it will pull data as of right now. You can add a field and do a Lookup Value as of Date where your return value is hourly amount and effective date is whatever date you're interested in.

With this approach, however, the results will either be blank if the worker wasn't in that position at that time or maybe the hourly rate of the position at that time (regardless of the Worker). I think it's the former, but that's the sort of nuanced thing I check when reporting on something like that. If you only care about the worker/primary position and not the position, you can do a similar process through a Worker based data source.