Maxx versus lastnonblank to fill in gaps based on row context

[Honne2021]

Hi, 

I have a very messy data (because the data is system generated)...it cannot be solved using just groupby (because of its size) so I decided to use calculated columns to clean it up. However, I noticed that I can only use the same code once in the sheet because an error on "circular dependency" occurs. I have attached image to explain better. Basically, I want the Final Country and Final Team columns to be: if the cell is blank, use the most recent data available.

The code I used (that worked for the Final Team column) is:

Final Team = var Team = CALCULATE(MAXX(EmployeeRecords,EmployeeRecords[New Team]))
Return IF(ISBLANK(New Team) , CALCULATE(LASTNONBLANK(EmployeeRecords[New Team],1),

FILTER('EmployeeRecords', EmployeeREcords[Worker ID] = EARLIER('EmployeeRecords'[Worker ID]))), EmployeeRecords[NewTeam])

But I get a circular dependency error if I do it for the Final Country.

[PaulDBrown]

If you need them as columns, the best option is to use the fill up function in power query. 
Sort the columns by Team and then by date (the latter in descending order). Make sure there are no blank values - if there are any, replace them with "null". Then create a new column and use the Group function to group by worker id and All rows as the aggregation.

 Before the next step, you need to edit the code in this step by replacing the underscore (see image)

by the following:

Table.FillUp(Table.FillUp(_,{"Fill up"}), {"Fill up"})

You can now expand the table (leave out the Worker ID column)

Sample file attached

[speedramps · Accepted solution]

Try this

Previous Team =

VAR mydate = SELECTEDVALUE('Table'[Date])    

VAR myteam = SELECTEDVALUE('Table'[Team])

VAR previousdate =  CALCULATE( MAX('Table'[Date]), 'Table'[Team] <> BLANK() && 'Table'[Date] < mydate )

VAR previousteam = CALCULATE( SELECTEDVALUE('Table'[Team]), 'Table'[Date] = previousdate )

RETURN

IF(myteam = BLANK(), previousteam, myteam)

Then add a teable vsula to the rpeorting canva with

worker, date and Previous Team

How it works ...

mydate and myteam gets the values foe the row context

previousdate gets the previous date for the worker

previousteam gets the previous team for the worker

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !