Finding current billing rate based on date worked


#1

Ok… I have 3 tables. 1) Date Table, 2) Data Table - a table listing hours worked by person and their position (this table also breaks down if the hours were at regular pay or overtime. There is a join between Data Table and Date Table via the Work date field. The 3) Rate Table - This table contains, the positions, the rate the person is paid, and the billable rate that is charged to the client. This table however, does not contain unique values (therefore I cannot connect the Rate Table to the Data Table) as it has a start and end date to each position and the applicable billable rates. For instance for the duration between a start date of March 1/2019 and March 20/2019 the billable rate maybe $20/hr. Then for the period of March 21/2019 to a plugged end date of Oct 31, 2099 the rate is $22/hr. I have been working with a Sumx and lookup measure that will go through the Data Table, find the current Work Date, then look to the Rate table and find the rate that is applicable based on the work date (work date >=start date && work date <=End date) but cannon seem to find a solution.
Any help would be appreciated.


#2

Please post some sample data if possible. Also, can you maybe bullet-point out what exactly you are after? That’s a lot of text

Enterprise%20DNA%20Expert%20-%20Small


#3

Nick,
Let me break it down this way.
Data Table (main columns below)
EmployeeNumber AlphaName WorkDate PayType Hours JobType JobLevel

This table will contain all the hours worked by our employees. I have created a new column in this table combining Job type and Level into one column.

Rate Table (main columns below)
Start End JobType JobLevel BaseHourlyRate ComponentA ComponentB BillableRate

Similar to Data Table, I have created a new column combining Job type and Level into one column.
Each singular row identifies the job type and level and the start and end dates that row.
For example:
Start End Job Type / Level Base Rate Component A Component B Billable Rate

3/1/2019 3/15/2019 xyz-123 $20.00 $2.00 $3.00 $25.00
3/16/2019 10/31/2099 xyz-123 $22.00 $2.20 $3.30 $27.50

As there can be multiple lines with the same Job Type / Level, I cannot connect the Data Table to the Rate table due to many to many relationships.

I am attempting via a measure to take the hours incurred by Workdate in the Data table, reach into the Rate Table, find the applicable row where based on the Workdate it finds the base rate that was in effect on that date. Additional calculations with the same logic can then occur on determining Billable rates, as well as the sum of components A and component B.

I attempted the formula below but it does not filter the Rate Table correctly to find only the jobtype / level that applies to the WorkDate period.

Base $ = CALCULATE(
    SUMX('Data Table',' Data Table'[Hours]*
    LOOKUPVALUE('RATES'[Base Rate ],RATES[Job Type/ Level],'Data Table'[Job Type / Level])),
    FILTER('Data Table','Data Table'[Pay Type]="1"),
    FILTER('Data Table',
            'Hours Table'[Work Date ]>=MIN('RATES'[Start]) &&
            'Hours Table'[Work Date ]<=MAX('RATES'[End])))

Does this help? Thanks Again.


#4

@djknicely
Yes, very much so. Thank you. Any possible way you can add the PBIX file?

Enterprise%20DNA%20Expert%20-%20Small