Cumulatively add multiplied values between two tables

[mikesdunbar]

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

[v-jianboli-msft · Accepted solution]

Hi @mikesdunbar ,

Please try:

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.

[amitchandak]

@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])))

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

Consider window function

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

[mikesdunbar]

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?