Value for the last row

[Anonymous]

Hi everyone, 

I want to have the last date, before the value in another column changes. 

However, the country Argentina should be ignored. So, I want to have a calculated column (with 1 for example) when it's the last time the players plays a match. 

So, in this example below: 

the column should show a 1 when Date = 16-5-2021 and squad Barcelona

[Greg_Deckler]

@Anonymous Try:

Column = 
  VAR __Squad = [Squad]
  VAR __Max = MAXX(FILTER('Table',[Squad] = __Squad),[Date])
RETURN
  IF([Date] = __Max,1,0)

[Anonymous]

@Greg_Deckler It works perfectly fine 🙂 

I thought I would be able to adjust to column to take into account not only one player, but the seven players I have in my table. 

But unfortunately, I run up against an another issue. This is the calculated column now:

Before Transfer =

VAR __Squad = [Squad]

VAR __Max = MAXX(FILTER('AllPlayers',[Squad] = __Squad),[Date])

VAR excludelastdate = //exclude the last date in general

IF(

'AllPlayers'[Date] = MAX('AllPlayers'[Date]),

1, BLANK())

VAR __MaxPlayer = CALCULATE(

MAX(AllPlayers[Date]),

FILTER(

AllPlayers,

AllPlayers[Player] = EARLIER(AllPlayers[Player]

)))

VAR __MaxDate = //exclude the last date of the match for each player

IF(

AllPlayers[Date] = __MaxPlayer,

1,

BLANK())

RETURN

IF(

[Date] = __Max &&

AllPlayers[Squad] <> "Argentina" && //exclude countries since this isn't a transfer to a club of course

AllPlayers[Squad] <> "France" &&

AllPlayers[Squad] <> "Norway" &&

AllPlayers[Squad] <> "Poland" &&

AllPlayers[Squad] <> "Portugal" &&

AllPlayers[Squad] <> "Sweden" &&

excludelastdate <> 1 &&

__MaxDate <>1,

1,

BLANK()

)

The issue is that once a squad occurs in the row, the calculated column won't give a '1' for a player for the last date of his club before the transfer is made. 

P.S.: not all players are included in the image. Ibrahimovic hasn't a value when he moves from Milan to PSG, but that is because Mbappe is moving to PSG a couple of years later.

[Greg_Deckler]

@Anonymous Can you post that sample data as text? Going to be difficult to move forward with this otherwise.

[Anonymous]

@Greg_Deckler of course, I have put the data in an Excel-file:
https://docs.google.com/spreadsheets/d/1dmTY3NGRpVXGE4Hxx_8ia8L9Lr-ni4Y1/edit?usp=sharing&ouid=11807...

[Anonymous · Accepted solution]

@Greg_Deckler  It is solved!

To avoid that a transfer wouldn't be visible due to a transfer to the same club by another player, I added a filter for Player. 

So, I extended the VAR __Max: 

VAR __Squad = [Squad]

VAR __Player = [Player]

VAR __Max = MAXX(FILTER('AllPlayers',[Player] = __Player && [Squad] = __Squad),[Date])

And that did the trick. Thanks for your initial measurement; that gave me the inspiration to work it out further.