Filtering Data based on most recent data labels

[Anonymous]

Hi, 

I'm hoping someone could help me. I've created a Power Bi report that will track when someone complets a task and whether they are due to complete the same task again (of it they've let it lapse and therefore it's due). I suppose you could say that this question is in two parts:

My table is set to show the current period based on the previous period, and as a lot of dates between periods may just be the same (because someone may not need to complete a task again for a year or two) I have used a MAX function to get the current date.

The problem I had is what about new starters and leavers who perhaps don't have a 'previous' or 'current' record because that information isn't in the data (as opposed to someone who has been in the team for a while and just hasn't done their work). So to fix this I created a couple of calculated columns to search for pervious/current names and then modified my measures to take this into account. This is my table currently:

My table is then filtered down using a team. All this works fine

The two part problem I have is:

Part 1: The colour coding works fine using this formula:

Colour = IF('Table1'[TaskminusDays] >=90,1, 

IF('Table1'[TaskminusDays] <90 && 'Table1'[TaskminusDays] >0,2,

IF('Table1'[TaskminusDays] <=0,3)))

Conditional Formatting - Background Colour

1 = Green

2 = Amber

3 = Red

Blank = Red

However I can't seem to figure out how to modify this (and the measures feeding into this) so that any new starters/leavers are just ignored and therefore their empty cell will be colourless. I can't just put Blank is no colour because what if someone has been in the team for say 5 years and just not logging their tasks correctly?

Part 2:

I've been looking through so many forums today to try and answer this question but I just can't seem to. 

What happens if someone moves between teams within the department???

Currently on the face of it my table looks like the above however Person1 moved teams within the last month. So if I filter down to their previous team is looks like this

Previous Team Filter

Table2

Current Team Filter

Table3

Desired Result: Current Team Filter (Person1 has been removed from the records of their previous team and therefore doesn't come up)

Table4

Please see attached a copy of my report with the some measures/caclulated columns in to make this all make sense (Some might be "broken" but they are written out as they are in my current report).

Dashboard (I've saved this to my OneDrive and put the link to it below)

https://1drv.ms/u/s!Anq4uQF2Yu4hoWorYjKqJtVmomTd?e=da0QLk 

Thank you for your time. 

E

[Anonymous]

Update: 

I managed to solve Part 1 above but altering the DAX formula used for my colour coding. 

Originally my colour coding was:

Current Colour = IF('Table1'[Measure] >=90,1,

IF('Table1'[Measure] <90 && 'Table1'[Measure] >0,2,

IF('Table1'[Measure] <= 0, 3)))

And the same formula for the 'Previous Colour'

I simply added another part to the If statement as shown below:

Current Colour = IF('Table1'[Measure] >=90,1 && 'Table1'[Measure2] >0,

IF('Table1'[Measure] <90 && 'Table1'[Measure] >0 && 'Table1'[Measure2]>0,2,

IF('Table1'[Measure] <= 0 && 'Table1'[Measure2] >0, 3,

IF('Table1'[Measure2] =0,4))))

And then added a new rule for 4 to turn the cells white. Measure 2 is a sum of two calculated columns that search to see whether the person/task/team were in the previous or current month.

Still stuck on Part 2, if there is anything that can be done. 

Kind regards,

E