This should be simple but I can't make it work. To calculate the Revenue from inception to date = I have a timesheet table of hours spent that spans multiple dates for multiple people doing multiple activities for multiple projects. I want to multiply the hours by a billable rate table where the rates differ by activity the multiply the results by a change factor (the billable rate is in British Pounds and I need to convert to USD) that changes each month. I have set a relationship to the Activities and the Date fields. I cannot figure out how to multiple all of the hours for one month by the rate for the activity and then adjust the result by the change factor for that month. GDP to USD TableDateExchange Rate11/30/20141.578412/31/20141.563441/31/20151.513692/28/20151.531543/31/20151.498034/30/20151.493535/31/20151.545866/30/20151.557267/31/20151.555738/31/20151.55865 Billabale Rate TableActivity NameRateAdministration£24.00Data Manipulate£32.50DCS Inbox£24.00Hardware & Software maintenance£24.00Hardware/Software£32.50Location Review£24.00Meetings£32.50On The Job Training£0.00Open time£0.00Procedure Review£0.00Processing£32.00Quality Control£32.00Scor Corrections£26.00Team Administration£24.00Training£32.00Underwriting Support£32.50 Timesheet TableDateNameActvity NameProject NameHours5/31/2016ChloeAdministrationU/W Support0.255/31/2016Â ChloeData ManipulateU/W Support4.506/1/2016Â VanessaClerical SupportU/W Support2.506/1/2016JessicaClerical SupportU/W Support8.006/1/2016RayneData ManipulateU/W Support3.006/1/2016RayneLocation ReviewU/W Support1.756/1/2016ChristineProcessingU/W Support5.006/1/2016GlennAdministrationU/W Support1.006/1/2016GlennData ManipulateU/W Support2.506/1/2016GlennHardware & Software maintenancU/W Support0.256/1/2016GlennTraining-general businessU/W Support3.50

Hi wparker5932 Multiply gets hour*billable rate based on each row context without taking any other condition into consideration. So it does not get the total hour*billable rate as you wanted. Multipleforonmonth sums the values in “Multiply” column based on the month/year and activity name, thus get the total hour*billable rate as you said. If my reply help you solve your problem,could you please accept it as a sloved solution? Best Regards Maggie

Forum Discussion

wparker5932

Frequent Visitor

8 years ago

Solved

Multiply hours by billable rate by change factor

This should be simple but I can't make it work.

To calculate the Revenue from inception to date = I have a timesheet table of hours spent that spans multiple dates for multiple people doing multiple activities for multiple projects. I want to multiply the hours by a billable rate table where the rates differ by activity the multiply the results by a change factor (the billable rate is in British Pounds and I need to convert to USD) that changes each month. I have set a relationship to the Activities and the Date fields. I cannot figure out how to multiple all of the hours for one month by the rate for the activity and then adjust the result by the change factor for that month.

GDP to USD Table

Date	Exchange Rate
11/30/2014	1.5784
12/31/2014	1.56344
1/31/2015	1.51369
2/28/2015	1.53154
3/31/2015	1.49803
4/30/2015	1.49353
5/31/2015	1.54586
6/30/2015	1.55726
7/31/2015	1.55573
8/31/2015	1.55865

Billabale Rate Table

Activity Name	Rate
Administration	£24.00
Data Manipulate	£32.50
DCS Inbox	£24.00
Hardware & Software maintenance	£24.00
Hardware/Software	£32.50
Location Review	£24.00
Meetings	£32.50
On The Job Training	£0.00
Open time	£0.00
Procedure Review	£0.00
Processing	£32.00
Quality Control	£32.00
Scor Corrections	£26.00
Team Administration	£24.00
Training	£32.00
Underwriting Support	£32.50

Timesheet Table

Date	Name	Actvity Name	Project Name	Hours
5/31/2016	Chloe	Administration	U/W Support	0.25
5/31/2016	Â Chloe	Data Manipulate	U/W Support	4.50
6/1/2016	Â Vanessa	Clerical Support	U/W Support	2.50
6/1/2016	Jessica	Clerical Support	U/W Support	8.00
6/1/2016	Rayne	Data Manipulate	U/W Support	3.00
6/1/2016	Rayne	Location Review	U/W Support	1.75
6/1/2016	Christine	Processing	U/W Support	5.00
6/1/2016	Glenn	Administration	U/W Support	1.00
6/1/2016	Glenn	Data Manipulate	U/W Support	2.50
6/1/2016	Glenn	Hardware & Software maintenanc	U/W Support	0.25
6/1/2016	Glenn	Training-general business	U/W Support	3.50

Need Help

v-juanli-msft
8 years ago
Hi wparker5932

Multiply gets hour*billable rate based on each row context without taking any other condition into consideration. So it does not get the total hour*billable rate as you wanted.

Multipleforonmonth sums the values in “Multiply” column based on the month/year and activity name, thus get the total hour*billable rate as you said.

If my reply help you solve your problem,could you please accept it as a sloved solution?

Best Regards

Maggie

6 Replies

v-juanli-msft
Community Support
8 years ago
Hi wparker5932

First, let me confirm some details with you.

1.“multiple all of the hours for one month by the rate for the activity”

As your example, the result of multiplying all of the hours for May of 2016 by the rate for the activity is as follows:

Result=0.25*24+4.5*32.5

2.As to the GDP to USD Table, it shows the different Exchange Rate for each month of different year.

For example, the Exchange Rate for November of 2014 is 1.5784, that for December is 1.56344.

3.“adjust the result by the change factor for that month”

Based on my two example above, If the Exchange Rate for May of 2016 is 1.2, so the final result should be:

Final result=(0.25*24+4.5*32.5)*1.2

Is my understanding right? If not, please point out the correct way.

Based on my understanding above, I test as follows.

1.In Query Editor, Split column for the column ”Rate” to extract the number from the string.

2.Create calculated column in GDP to USD Table

month/year = CONCATENATE(CONCATENATE(MONTH([Date]),"/"),YEAR([Date]))

3. create columns in Timesheet Table

month/year = CONCATENATE(CONCATENATE(MONTH([Date]),"/"),YEAR([Date]))

mutiply = [Hours]*RELATED('Billabale Rate'[Rate])

multipleforonmonth = CALCULATE(SUM([mutiply]),FILTER(ALL(Timesheet),[month/year]=EARLIER([month/year])))

final result = [multipleforonmonth]*RELATED('GDP to USD'[Exchange Rate])

Here is my pbix.

Best Regards

Maggie
- wparker5932
  Frequent Visitor
  8 years ago
  There is one step that confuses me and it may be why the results appear to be incorrect.
  
  I'm not sure how multipleforonmonth = CALCULATE(SUM([mutiply]),FILTER(ALL(Timesheet),[month/year]=EARLIER([month/year]))) works. I am assuming it should sum the total hour*billable rate, by month, by activity name. The results are coming out much too high (billions of $ when it should be hundreds of thousands of $).
  
  My relationships match your example and the formulas as well.
  
  What am I missing?
  - v-juanli-msft
    Community Support
    8 years ago
    Hi wparker5932
    
    “it should sum the total hour*billable rate, by month, by activity name”
    
    I modify “multipleforonmonth” as below
    
    multipleforonmonth = CALCULATE ( SUM ( [mutiply] ), FILTER ( ALLEXCEPT ( Timesheet, Timesheet[Actvity Name] ), [month/year] = EARLIER ( [month/year] ) ) )
    
    If it is still not correct, please review the example where I described at the first of my reply to let me know if I misunderstand something.
    
    Best Regards
    
    Maggie