Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
HassanAshas
Helper V
Helper V

Make a Calculated Column based upon multiple values from current row as well as previous row

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, 

 

DateEmp CodeProject CodeAllocationRevenue
11/1/2022 0:001A11160
12/1/2022 0:001A11160
1/1/2023 0:001A1 
2/1/2023 0:001A1 
3/1/2023 0:001A1 
11/1/2022 0:002B0.45544
11/1/2022 0:002C0.62000
12/1/2022 0:002B0.64032
12/1/2022 0:002C0.31000
1/1/2023 0:002B0.6 
1/1/2023 0:002C0.4 
2/1/2023 0:002B0.6 
2/1/2023 0:002C0.4 
3/1/2023 0:002B0.6 
3/1/2023 0:002C0.4 
11/1/2022 0:003B14788
12/1/2022 0:003B0.754284
1/1/2023 0:003B1 
2/1/2023 0:003B1 
3/1/2023 0:003B1 
11/1/2022 0:004Bench10
12/1/2022 0:004Bench10
1/1/2023 0:004B1 
2/1/2023 0:004B1 
3/1/2023 0:004B0.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) 

 

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 

 

HassanAshas_0-1674830243187.png

 

Can anyone help me out in this?

 

 

 

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! 

0 REPLIES 0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.