Apply rate that is between 2 dates

[lekkerbek]

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 🙂

[LivioLanzo]

Hi @lekkerbek

can the rate vary by Employee? Could you post a better sample in tabular format?

thx

[lekkerbek]

Hi @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 · Accepted solution]

@lekkerbek

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.

[lekkerbek]

Thank you very much @LivioLanzo. It works like a charm!

🙂