Help in Creating Calculated Column using multiple values from current and last row of table

[HassanAshas]

Hi, I have a dataset of employees working on month-to-month basis on different projects. Every row of the dataset uniquely identifies an employee working on one project in one month. Furthermore, his revenue and allocation on that project for that month is also recorded in that row (Also, for one month, the cummulative sum of the allocation will never exceed "1" for any employee) 

The dataset looks something like this, 

Date	Emp Code	Project Code	Allocation	Revenue
11/1/2022 0:00	1	A	1	1160
12/1/2022 0:00	1	A	1	1160
1/1/2023 0:00	1	A	1
2/1/2023 0:00	1	A	1
3/1/2023 0:00	1	A	1
11/1/2022 0:00	2	B	0.4	5544
11/1/2022 0:00	2	C	0.6	2000
12/1/2022 0:00	2	B	0.6	4032
12/1/2022 0:00	2	C	0.3	1000
1/1/2023 0:00	2	B	0.6
1/1/2023 0:00	2	C	0.4
2/1/2023 0:00	2	B	0.6
2/1/2023 0:00	2	C	0.4
3/1/2023 0:00	2	B	0.6
3/1/2023 0:00	2	C	0.4
11/1/2022 0:00	3	B	1	4788
12/1/2022 0:00	3	B	0.75	4284
1/1/2023 0:00	3	B	1
2/1/2023 0:00	3	B	1
3/1/2023 0:00	3	B	1
11/1/2022 0:00	4	Bench	1	0
12/1/2022 0:00	4	Bench	1	0
1/1/2023 0:00	4	B	1
2/1/2023 0:00	4	B	1
3/1/2023 0:00	4	B	0.8

I need to calculate the Projected Revenue of all the employees for the current (Today()) and the next Months in the dataset. What I am trying to achieve is to create a new Calculated Column in the dataset (because that's what I believe should be done). 

The logic for calculating the Projected Revenue of the Current and Next Month is as follows (this logic will give same value for all the next months, barring any exceptional case) 

• The Projected Revenue for January 2023 (Today()) will be equal to the Revenue gained by that employee on that project in the last month (December 2023). It will also take in consideration the allocation the person had in Current Month and Last Month. So, let's say if Emp 1 was allocated 100% on Project A in December 2022 and earned $2000, and his projected allocation on Project A is 100% then his revenue will also be $2000 for January 2022 for that specific Project. If his projected allocation is 50% then his revenue for this project will be $1000. 
• If the person was on "Bench" in December (Last Month) and now in next months is allocated to any "Project" then his projected revenue for the next months will be equal to the average revenue of that project in last month from all employees allocated to that project.

The problem that I am facing is this actually requires me to use Multiple values from current row as well as the previous Row. What I tried to do was to use Lookupvalue function. I tried to get the current row's Emp_ID, Date and Project_Code (because that uniquely identifies a record) and using that, I tried to look up the Revenue and Allocation Value by subtracting one month from the Date. However, it didn't work for me and gave me an error that I was looking at multiple records (not sure why as my dataset was quite huge so I couldn't figure it out) 

The Result (with this given sample dataset) that I am trying to achieve is as given below (in Projected Revenue Column),

To explain how it is being calculated for each employee, 

For Emp ID: 1, Project: A
December Allocation: 1 
December Revenue: 1160 
January Projected Allocation: 1
January Projected Revenue: Projected Allocation * Last_Month_Revenue = 1 * 1160 = 1160 

For Emp ID 2, we have two Projects. 

For Project B: 
December Allocation: 0.6 
December Revenue: 4032 (if allocation was 1 then revenue would be 4032 / 0.6 = 6072) 
January Projected Allocation: 0.6 
January Projected Revenue: 0.6 * 6072 = 4032 

For Project C: 
December Allocation: 0.3 
December Revenue: 1000 (with allocation = 1, revenue would be 1000 / 0.3 = 3333.33) 
January Projected Allocation: 0.4 
January Projected Revenue: 0.4 * 1000 = 1333.33

For Emp ID 3, Project B 
December Allocation: 0.75 
December Revenue: 4284 (with allocation = 1, revenue: 5712) 
January Projected Allocation: 1
January Projected Revenue: 1 * 5712 = 5712 

For Emp ID 4, Project B 
December Project: Bench 
January Project: B 
Average of Project B: ((4032 / 0.6) + (4284 / 0.75)) / 2 = 6216
January Projected Allocation: 1 
January Projected Revenue: 1 * 6216 = 6216 

Then, I also tried to use Calculate function to get the value of Last Month's Revenue and fill up the whole new column with that value (for now), but again this acted differently. It only filled up the values for the Last Month, and not for the months I wanted it to fill the values for. This is the measure I tried 

Projected Revenue = 
var last_row_year = YEAR(EDATE(TODAY(), -1))
var last_row_month = MONTH(EDATE(TODAY(), -1))

var value_amount = 
CALCULATE(
    SUM('data'[Revenue]),
    YEAR(data[Date]) = last_row_year, 
    MONTH(data[Date]) = last_row_month
)
RETURN 
value_amount

But it didn't work for me and only filled up the values for December 2022 

Can anyone help me out in this? I only need to understand how to utilize values from multiple rows in the Calculated Column Field. Other Logic, I can make myself but to give an understanding of what exactly I am trying to do, I decided to make a sample example for the complete problem. Thank you. 

You can download the Power BI File from here if needed: https://drive.google.com/file/d/1YGXxHZqdZO9IwFmmkf1dSrv_b9LSpdO6/view?usp=sharing

Thanks a lot in advance!