Find MAX QTR within a Year based on Criteria

[AlwaysAGooner]

Hi Team,

Need to find the MAX value for a year based on the Criteria. In the below table,  we have values for COuntry & Brand combincation appearing for each QTR, need to find the MAX QTR for 2023 and likewise for 2024. Highlighted Calculated Column is what I need.

If Italy Ferrari has 3 QTR, 1, 2 & 3 then against Q3 2023, it should put 2023, likewise for 2024.

Thanks!

[AlwaysAGooner · Accepted solution]

Thanks for the response.
Managed to crack it on my own, did it slighltly differently

Var MaxQTR_Year = 
If( MAX ( Table[QuarterYear] ) = 
Calculate(
MAX ( Table[QuarterYear] ),
Allexcept(Table, Table[Country], Table[Brand], Table[QuarterYear]),
Right(Table[QuarterYear],4),
Blank()
)
Return MaxQTR_Year

[govindarajan_d]

Hi @AlwaysAGooner,

Can you try the below formula in a measure? 

This assumes you have QuarterYear like you have in the question and Calendar Year as well. Replace 'Table' with your table name. 

Explanation: We get the QuarterYear for the corresponding row and then we have a summarized table that contains the max quarter for each year. We filter that table with current row's QuarterYear and then check the count. If it is 1, we use the corresponding year value or else Blank. 

YearOnMaxQ =
VAR __CurrentRow =
    MAX ( Table[QuarterYear] )
VAR __CheckIfMaxQ =
    FILTER (
        SUMMARIZE (
            ALL ( Table ),
            Table[Year],
            "MaxQuarter", MAX ( Table[QuarterYear] )
        ),
        [MaxQuarter] = __CurrentRow
    )
VAR __CorrespYear =
    MAXX ( __CheckIfMaxQ, [Year] )
VAR __FinalYear =
    IF ( COUNTROWS ( __CheckIfMaxQ ) = 1, __CorrespYear, BLANK () )
RETURN
    __FinalYear

Tested:

Upvote and accept as solution if it helps!