Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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
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?
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
Maggie,
Here is what I am seeing. I created a page that displays the Month/Year from the GDP to USD table, the Month/Year from the Timesheet Table, and multiply, multiplyforonemonth and final result from the Timesheet table.
I then used a filter to display just one month/year (3/2018).
I believe what I should see is that multiply ($16,588.75) and multipleforonemonth ($28,742,322.00) should be equal but they are not.
The final result appears to be applying the correct factor of 1.4082
What does multipleforonemonth do that is a next step after multiply?
If I change to: final result = [mutiply]*RELATED('GBP to USD'[Rate]), I believe I have the correct answer ($23,360) but I would still like to know what multipleforonemonth is intended to calculate.
(Sorry, I have screen captures to share but for the life of me, cannot figure out how to insert them using the icon above)
.
Best regards,
W. Parker
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |