Forum Discussion

ajinkya1907's avatar
ajinkya1907
Helper II
8 years ago
Solved

Problem in selecting Max date

 

I have a table "matches" with below shown columns

 

 

I have created calculated field

Max_date = MAX(matches[date])

 

I have created a filter based on year. I want team1 value to be displayed for the max date of that year. The simle formula which I can think is 

winning_Team = Measure = IF(matches[date] = matches[Max_date], matches[team1])

 

Problem is such comparison is invalid in power BI. It is not giving me fields matches[date] & matches[team1] in the if statement at all. I tried using some aggregation function like MAX(matches[date]) but it gives invalid results.

 

Please let me know if there is any better solution or if any turn around.

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    ajinkya1907

     

    Try this MEASURE to get the TEAM1 on maximum date

     

    Team1 On MaxDate =
CALCULATE (
    FIRSTNONBLANK ( Matches[Team1], 1 ),
    FILTER ( ALL ( Matches[Date] ), Matches[Date] = MAX ( Matches[Date] ) )
)

9 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    ajinkya1907

     

    Try this MEASURE to get the TEAM1 on maximum date

     

    Team1 On MaxDate =
CALCULATE (
    FIRSTNONBLANK ( Matches[Team1], 1 ),
    FILTER ( ALL ( Matches[Date] ), Matches[Date] = MAX ( Matches[Date] ) )
)