Forum Discussion
Helper IVApply rate that is between 2 dates
Hi,
I'm having trouble writing a Dax code or perhaps I should be using a new column.
I have a dataset with the following tables:
- Employees with internal rate that vary each month, so for example:
start date: 1-1-2019 (D-M-Y)
end date 31-1-2019
Rate = € 100
start date 1-2-2019
end date 28-2-2019
Rate = € 110
- ProjectTimeTransactions
This table shows a line with a date, employee ID, nr of hours spend on a project, so for example
15-1-2019, project X 2 hours
17-1-2019 project X 3 hours
10-2-2019 project X 1 hour
The output should be:
15-1-2019 project X 2 hours x € 100 = € 200
17-1-2019 project X 3 hours x € 100 = € 300
10-2-2019 project X 1 hour x € 110 = € 110
Can somebody put me in the right direction? Thanks :-)
You can import the rate into the fact table.
First you need to replace the blank values (with Power Query) within the Rates table (column End Date) with a big date (for example 12/31/9999).
Then you can add a column in the fact table with this DAX:
Rate = CALCULATE( VALUES( Rates[InternalRate] ), Rates[StartDate] <= EARLIER(Data[Date]), Rates[EndDate] >= EARLIER(Data[Date]), Rates[EmployeeID] = EARLIER(Data[EmployeeID]) )then your projection costs can be calculated on the fly without needing to add a calculated column for those. I would recommend to add the calculated column with the Rate as I did and not multiply it by Quantity within the calculated column in order to get a better compression (Rates have less unique values). If you're extracting data from a database then you can join the fact table to the Rates table in order to retrieve the correct rate:
SELECT FactTable.*, Rates.Rate
FROM FactTable INNER JOIN Rates
ON FactTable.EmployeeID = Rates.EmployeeID AND FactTable.Date BETWEEN Rates.StartDate and Rates.EndDate
Another alternative can also be to create the below model and not add the calculated column with the Rate but retrieve at query time but I think you will get a poorer performance so I'd go with the first option.
4 Replies
- LivioLanzo
Solution Sage
lekkerbekHi LivioLanzo,
Yes the rates vary and the current month has no enddate. It could be that the rate doesn't change for the next month. The two tables look like this:
The projects costs (in the project time table) is obviously the desired outcome of the formula which is currently blank in my table. Formula is quantity x internal rate (but depends on the employee and the date).
Employee (the end date in December 2018 is blank intentionally):
EmployeeID StartDate EndDate InternalRate 1 1-1-2018 31-1-2018 € 71,00 1 1-2-2018 28-2-2018 € 71,10 1 1-3-2018 31-3-2018 € 71,20 1 1-4-2018 30-4-2018 € 71,30 1 1-5-2018 31-5-2018 € 71,40 1 1-6-2018 30-6-2018 € 71,50 1 1-7-2018 31-7-2018 € 71,60 1 1-8-2018 31-8-2018 € 71,70 1 1-9-2018 30-9-2018 € 71,80 1 1-10-2018 31-10-2018 € 71,90 1 1-11-2018 30-11-2018 € 72,00 1 1-12-2018 € 72,10 2 1-1-2018 31-1-2018 € 50,00 2 1-2-2018 28-2-2018 € 51,00 2 1-3-2018 31-3-2018 € 52,00 2 1-4-2018 30-4-2018 € 53,00 2 1-5-2018 31-5-2018 € 54,00 2 1-6-2018 30-6-2018 € 55,00 2 1-7-2018 31-7-2018 € 56,00 2 1-8-2018 31-8-2018 € 57,00 2 1-9-2018 30-9-2018 € 58,00 2 1-10-2018 31-10-2018 € 59,00 2 1-11-2018 30-11-2018 € 60,00 2 1-12-2018 € 61,00 Project time:
ActivityDescription Currency Date EmployeeID ProjectCode ProjectDescription Quantity Project costs WP7 - RP2 - Hours EUR 31-8-2018 1 500 Project A 28,5 € 2.043,45 WP2 - RP2 - Hours EUR 28-2-2018 1 600 Project B 40,01 € 2.844,71 WP1 - RP1 - Hours EUR 30-4-2017 2 700 Project C 8,6 € 455,80 WP1 - RP2 - Hours EUR 30-4-2018 2 800 Project D 3 € 159,00 - LivioLanzo
You can import the rate into the fact table.
First you need to replace the blank values (with Power Query) within the Rates table (column End Date) with a big date (for example 12/31/9999).
Then you can add a column in the fact table with this DAX:
Rate = CALCULATE( VALUES( Rates[InternalRate] ), Rates[StartDate] <= EARLIER(Data[Date]), Rates[EndDate] >= EARLIER(Data[Date]), Rates[EmployeeID] = EARLIER(Data[EmployeeID]) )then your projection costs can be calculated on the fly without needing to add a calculated column for those. I would recommend to add the calculated column with the Rate as I did and not multiply it by Quantity within the calculated column in order to get a better compression (Rates have less unique values). If you're extracting data from a database then you can join the fact table to the Rates table in order to retrieve the correct rate:
SELECT FactTable.*, Rates.Rate
FROM FactTable INNER JOIN Rates
ON FactTable.EmployeeID = Rates.EmployeeID AND FactTable.Date BETWEEN Rates.StartDate and Rates.EndDate
Another alternative can also be to create the below model and not add the calculated column with the Rate but retrieve at query time but I think you will get a poorer performance so I'd go with the first option.