Apply a factor to measure based on date range table

[pheukshorst]

I have a date range table by account number with a factor (i.e. the fuel surcharge).

AccountNumber	dateFrom	dateTo	Fuel Surcharge(%)
AA	1/1/2023	1/31/2023	5
BB	1/1/2023	1/31/2023	6
AA	2/1/2023	2/28/2023	7
BB	2/1/2023	2/28/2023	8

I have a DAX measure called "Cost". 

I want a new "Cost+Fuel" measure that factors in the fuel surcharge based on the account and 

date the transaction occured, but I don't know how to make this happen.

Currently I have a relationship on the lookup table's AccountNumber to the transactional data but no relationship on the dateFrom or dateTo columns.

Here is what I need the "Cost Plus Fuel" measure to equal.

AccountNumber	Date	Cost	Cost Plus Fuel
AA	1/20/2023	100	105
BB	2/20/2023	100	106
AA	1/20/2023	100	107
BB	2/20/2023	100	108

[DataNinja777 · Accepted solution]

Hi pheukshorst,

Thanks for the sample numbers. I am assuming you meant that the dates in the Cost Plus Fuel table is like as shown in the screen print below instead of two January dates and two February dates for AA and BB Account Numberes respectively.  

With this assumption, I have copied and pasted the rest of the sample data table you've prepared, and created the data model like shown below:

Please note that, only the Cost Plus Fuel table has a relationship with the calendar table, while Fuel Surcharge table with the duration information of the applicable surcharge does not have any relationship with the calender table.  Also, I've created a dimension table for Account Number in order to avoid many-to-many relationship which creates ambiguity and confusion in the data model.  I've then created many-to-one relationships for the Account Number dimension table with the fact tables. 

Next, I created a dax measure expression to capture the cost plus percentage for a selected date which falls within the duration range.  

Next, I've created a calculated column which wraps the measure created above with Calculate function to respect the row context of this table. 

The result generated is as you have requested.  Please let me know if you'd like to have the pbix file for this task.  

[pheukshorst]

Note: I pasted the tables from excel and didn't notice that the column data isn't separated very well anymore.

The fuel rates in the first table are simply 5, 6, 7 and 8.

The "Cost" in the 2nd table is 100 for all 4 rows.