cancel
Showing results for
Did you mean:
Frequent Visitor

Cumulatively add multiplied values between two tables

Trying to make a cumulative sum of hours multiplied by Billing Rate. The two columns are linked by the User. There are two hours columns: Approved Hours and Booked Hours.

Approved hours should all be in the past (assuming today is 12/10/2022), and booked hours are what we are planning the person will work.

What I’m trying to accomplish here is to have a measure (or calculated column) that is a cumulative sum of all the approved hours up until today, and then after that add to those values what the Billing Cost is multiplied by Booked Hours. The purpose of this is to see if the hours in the past push the total amount of cost past the budgeted amount. Think of it like this: when all of the booked hours were loaded, the budget is spot on. If someone worked more than the budgeted hours (which would be logged in the Approved Column), and the future hours (logged in the Booked Column) stayed the same, then the final amount would be over.

Hours Report

 Date Job code User Booked Hours Approved Hours 12/5/2022 S - ME Sr. Engineer Allan Tran 4 5 12/5/2022 S - ME Engineer Brad Roth 6 1 12/5/2022 S - ME Engineer Craig Jones 4 0 12/5/2022 S - QA Quality Engineer Dustin Kaiser 0 1 12/5/2022 S - EE Engineer Ed Hayes 0 1 12/5/2022 S - PM Project Manager Elaine Light 8.62 9 12/5/2022 S - EE Sr. Engineer Jessica Miller 0 0 12/5/2022 S - EE Engineer John Smith 0 0 12/5/2022 S - SE Engineer Liz Elliot 0 0 12/5/2022 S - SE Sr. Engineer Steve Kent 0 0 12/10/2022 S - ME Sr. Engineer Allan Tran 30.82 40 12/10/2022 S - ME Engineer Brad Roth 22 3 12/10/2022 S - ME Engineer Craig Jones 4.4 3 12/10/2022 S - QA Quality Engineer Dustin Kaiser 9.89 1.25 12/10/2022 S - EE Engineer Ed Hayes 22 21 12/10/2022 S - PM Project Manager Elaine Light 10.53 16 12/10/2022 S - EE Sr. Engineer Jessica Miller 5.27 7 12/10/2022 S - EE Engineer John Smith 36 32.75 12/10/2022 S - SE Engineer Liz Elliot 0 1 12/10/2022 S - SE Sr. Engineer Steve Kent 5.52 0 12/17/2022 S - ME Sr. Engineer Allan Tran 25.22 26.5 12/17/2022 S - ME Engineer Brad Roth 18 4.5 12/17/2022 S - ME Engineer Craig Jones 3.6 13 12/17/2022 S - QA Quality Engineer Dustin Kaiser 8.09 1.25 12/17/2022 S - EE Engineer Ed Hayes 30 23 12/17/2022 S - PM Project Manager Elaine Light 4.79 5 12/17/2022 S - EE Sr. Engineer Jessica Miller 4.31 6.5 12/17/2022 S - EE Engineer John Smith 26 25 12/17/2022 S - SE Engineer Liz Elliot 0 0.5 12/17/2022 S - SE Sr. Engineer Steve Kent 4.51 0 12/24/2022 S - ME Sr. Engineer Allan Tran 0.18 0 12/24/2022 S - ME Engineer Brad Roth 0.13 0 12/24/2022 S - ME Engineer Craig Jones 0 0 12/24/2022 S - QA Quality Engineer Dustin Kaiser 0.06 0 12/24/2022 S - EE Engineer Ed Hayes 0 0 12/24/2022 S - PM Project Manager Elaine Light 0.06 0 12/24/2022 S - EE Sr. Engineer Jessica Miller 0.03 0 12/24/2022 S - EE Engineer John Smith 0 0 12/24/2022 S - SE Engineer Liz Elliot 0 0 12/24/2022 S - SE Sr. Engineer Steve Kent 0.03 0 12/31/2022 S - ME Sr. Engineer Allan Tran 18.91 0 12/31/2022 S - ME Engineer Brad Roth 13.5 0 12/31/2022 S - ME Engineer Craig Jones 9.68 0 12/31/2022 S - QA Quality Engineer Dustin Kaiser 0 0 12/31/2022 S - EE Engineer Ed Hayes 22 0 12/31/2022 S - PM Project Manager Elaine Light 4.31 0 12/31/2022 S - EE Sr. Engineer Jessica Miller 3.23 0 12/31/2022 S - EE Engineer John Smith 25.65 0 12/31/2022 S - SE Engineer Liz Elliot 0 0 12/31/2022 S - SE Sr. Engineer Steve Kent 3.39 0 1/7/2023 S - ME Sr. Engineer Allan Tran 30.82 0 1/7/2023 S - ME Engineer Brad Roth 22 0 1/7/2023 S - ME Engineer Craig Jones 15.78 0 1/7/2023 S - QA Quality Engineer Dustin Kaiser 9.89 0 1/7/2023 S - EE Engineer Ed Hayes 41.8 0 1/7/2023 S - PM Project Manager Elaine Light 10.53 0 1/7/2023 S - EE Sr. Engineer Jessica Miller 5.27 0 1/7/2023 S - EE Engineer John Smith 41.8 0 1/7/2023 S - SE Engineer Liz Elliot 0 0 1/7/2023 S - SE Sr. Engineer Steve Kent 5.52 0

Employee Billing Rates

 Job Code Billing Rate (USD) Engineering Discipline User S - EE Engineer 180 Electrical John Smith S - EE Engineer 180 Electrical Ed Hayes S - EE Sr. Engineer 200 Electrical Jessica Miller S - ME Engineer 185 Mechanical Brad Roth S - ME Engineer 185 Mechanical Craig Jones S - ME Sr. Engineer 200 Mechanical Allan Tran S - PM Project Manager 190 Project Manager Elaine Light S - PM Project Manager 190 Project Manager Mike Brooks S - QA Quality Engineer 200 Quality Dustin Kaiser S - SE Engineer 200 Systems Liz Elliot S - SE Sr. Engineer 275 Systems Steve Kent
1 ACCEPTED SOLUTION
Community Support

Hi @mikesdunbar ,

``````Measure =
var _a = MAX('Employee Billing Rates'[Billing Rate (USD)])
var _b = CALCULATE(SUM('Hours Report'[Approved Hours]),FILTER('Hours Report',[Date]<=DATE(2022,12,10)))//assuming today is 12/10/2022, you can replace DATE(2022,12,10) with TODAY()
var _c = CALCULATE(SUM('Hours Report'[Booked Hours]),FILTER('Hours Report',[Date]>DATE(2022,12,10)))
return _b+_c*_a``````

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Hi @mikesdunbar ,

``````Measure =
var _a = MAX('Employee Billing Rates'[Billing Rate (USD)])
var _b = CALCULATE(SUM('Hours Report'[Approved Hours]),FILTER('Hours Report',[Date]<=DATE(2022,12,10)))//assuming today is 12/10/2022, you can replace DATE(2022,12,10) with TODAY()
var _c = CALCULATE(SUM('Hours Report'[Booked Hours]),FILTER('Hours Report',[Date]>DATE(2022,12,10)))
return _b+_c*_a``````

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

@mikesdunbar , I Hope the Approved hours is a column or a measure in the first table shown in digram on many side

Cumm Sales = CALCULATE(SUMC(Table1, Table[Approved hours] * related('Billing Rate'[Billing Rate])) ,filter(all('Date'),'Date'[date] <=max('Date'[date])))

Consider window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Frequent Visitor

It's not letting me use that formula. I don't have a specific Date table. Is that why?

If I made a Date Table with Calendar Auto, and join them, I only get values for that week, and only until today. After today, I wanted to switch the summation to the Booked hours. Does that make sense?