Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Value for the last row

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 exam...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous Try:

Column = 
  VAR __Squad = [Squad]
  VAR __Max = MAXX(FILTER('Table',[Squad] = __Squad),[Date])
RETURN
  IF([Date] = __Max,1,0)
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

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