Using different rates across different time periods

[Aedmunds]

Hi Gurus

So I have an interesting one ... 

I have the following tables and fields

Resource

- FullName

Resource$

- Rate

- Start Date (new)

- End Date (new)

Timesheet

- Days

Obviously the Calendar table is connected to both to give me Time Intelligence and the Rate connects to the Resource as well.

And the current Measure is Total Cost $ = Sum (Timesheet.Days * Resource.Rate)

What I need to do is add a Start and End to the Resource$ Table against each rate, so I can do multiple calculations across timespans.

This needs to be super efficient, so if I have 500 resources banking several hundred records a week, I need the calcs to be efficient and fast.

Is there a clean way to adjust the formula to fix this ... or do I need to start doing Merges and Expands and Joins across tables?

Example of what the data is going to look like and the results sought

John (Resource) - $1000 (Rate) from 1 Jul 22 (Start Date) to 30 Jun 23 (End Date) - 200 (days) = $200,000 (Total Cost $)  

John - $1100 from 1 Jul 23 to 30 Jun 24 - 200 days

In this example, my current formula doesn't understand the start and end, and will come out with $400,000 but I want it to come out with $420,000..

Cheers

Andre

[j_ocean]

I assume you have name in all the tables?

You can create a custom column in your resources as follows

{
Number.From([Start Date])
 .. 
 Number.From([End Date])
 }

 You can then expand it, creating a table of every person with their rate for every day.

Then you can do a left outer merge with time sheet name+date on the left to resources name+date, putting everyones rate for every day that they billed on the same line and dropping unbilled days.

Edit: if you have the option of using an integer ID number the merge will go much faster than a text string.

[Aedmunds]

I know potentially answering my own question, but while researching just came across DATESBETWEEN ... could it be as simple as adding DATESBETWEEN (Start, End) on the formula above ... doesn't seem so.  Too easy and trying to think how it would know the dates ... although it understands the Timesheet date from the calendar table.

Or another option, expand the Resource$ table for every resource with every date range so it effectively creates a table of resource$, rates and dates.

I'm thinking of other ways to tackle this one.