cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jpbi94
Resolver II
Resolver II

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 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

 

TransferMessi.png

1 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.

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@Jpbi94 Try:

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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()
)
 
Transfers.png
 
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.

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.