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.
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
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.
Nick, just following up on this? I had sent the Pbix file… In the interim I found a work around by expanding the rate table to include all possible dates between the Start and End date of an applicable rate for each Job type / level. By doing this it allowed me to use Lookup to obtain the rate from the rate table whereby the job type / level match, and the work date (in which the hours were worked) match the newly added per day date field in the rate table.
Thx
Don
@djknicely,
Here’s what I came up with. I went ahead and changed the data model around though, as this was more of data modeling issue more than anything. There’s a parameter to enter in the file location and file name of the file. I made an excel file from your pbix file. Just change that parameter to wherever you put the excel file. Just be sure to include the full file location and path. Anyhow…
-
Created a DimEmployee and DimJobTypeStep table. Did this in power query so you can see those steps. Nothing really that crazy. In doing that, I removed the AlphaName from the FactData table.
-
We will use those two Dim tables, along with the DimDate table for all of our filters (rows, columns, slicers, etc.)
-
With this data model, we can filter Fact Data by all three of those Dim table
-
Then we can create a many-to-many relationships from the FactData table to the FactRate table
We can put Alpha Name (from Dim Employee) and Job Step and Job Type (from DimJobTypeStep) and Dates from DimDate on rows.
Then we can use these two measures:
Total Base Rate =
SUM ( FactRate[Base Rate for Billing Rate Calc] )
Base Rate for Billing Cylce =
CALCULATE(
[Total Base Rate],
FILTER(
FactRate,
MAX( DimDate[Date]) >= FactRate[Start]
&& MAX( DimDate[Date]) <= FactRate[End]
)
)
Which then produces this table:
Here are the files:
Find Rate between two dates.pbix (499.5 KB)
Data.xlsx (331.4 KB)
Let me know if that is what you had in mind.
-Nick
Thx Nick I will give it a try and circle back.
Don