How to calculate consecutive count (based on various conditions)

[teejay]

Hi Guys,

Looker rookie here. I’m trying to determine a way to calculate a consecutive count and am not sure how to go about it. My data is structured as follows:

Month | Person | Award

2019-01, Yoda, Master

2019-05, Skywalker L, Master

2021-06, Han Solo, Rebel

2020-04, Darth Vader, Sith

I can currently calculate the total count of an award based on a particular month using the following:

  dimension: monthfilter {
    label: "Yes/No"
    type: yesno
    sql: extract (month from ${TABLE}.month) = 6 ;;
    hidden: yes
  } 

  dimension: uptonow {
    type: yesno
    sql:   ${TABLE}.month < current_date() ;;
    hidden: yes
  } 

  measure: mastercount{
    label: "Master Count"
    type: count_distinct
    sql: ${month_month} ;;
    filters: [monthfilter: "Yes",Award: "Master",uptonow: "Yes"] 
  }

Would someone be able to point me in the right direction in order to answer the following:

• Number of consecutive times a person has achieved X award status at the end of the FY
• What start and end year the ‘streak’ occurred

Any help would be much appreciated!

TJ