Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

Consecutive champions

I am in development of a practice and I have a table, where I am asked to make a formula to tell me which were the clubs that won in consecutive years.

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  Syndicate_Admin

     

     You can create a new column

    Is continuss_ = VAR A=FILTER('Table (2)','Table (2)'[Team]=EARLIER('Table (2)'[Team])&&'Table (2)'[Column]=EARLIER('Table (2)'[Year])) 
VAR B=FILTER('Table (2)','Table (2)'[Team]=EARLIER('Table (2)'[Team])) 
VAR C=LEFT(MINX(B,[Year]),4)+0=LEFT(MAXX(B,[Year]),4)-COUNTROWS(B)+1 
RETURN IF(COUNTAX(A,[Team])>0,TRUE(),IF(C=TRUE()&&COUNTROWS(B)>1,TRUE(),FALSE()))

     

    Is this the output you want?

     

     

     

    Best Regards,

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    Please provide the sample data in usable format, not as a screenshot.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Syndicate_Admin

     

     You can create a new column

    Is continuss_ = VAR A=FILTER('Table (2)','Table (2)'[Team]=EARLIER('Table (2)'[Team])&&'Table (2)'[Column]=EARLIER('Table (2)'[Year])) 
VAR B=FILTER('Table (2)','Table (2)'[Team]=EARLIER('Table (2)'[Team])) 
VAR C=LEFT(MINX(B,[Year]),4)+0=LEFT(MAXX(B,[Year]),4)-COUNTROWS(B)+1 
RETURN IF(COUNTAX(A,[Team])>0,TRUE(),IF(C=TRUE()&&COUNTROWS(B)>1,TRUE(),FALSE()))

     

    Is this the output you want?

     

     

     

    Best Regards,

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.