Forum Discussion

wparker5932's avatar
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 multi...
Need Help
  • v-juanli-msft's avatar
    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

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
8 years ago
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's avatar
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?