Calculated Column to find most recent Year with zero balance in Power BI

Question

I am working on a table with columns customerID, Year and Sales Amount and Bal Units. I want a calculated column that checks for the Bal Units and return the most recent year it got less then 1. If its not <1 then return blank. The structure might be like this.

I tried the query below but couldn't find the recent year rather it gives the current year

FirstYearWhenZero = 
VAR CurrentCustomerID = 'Customer Table'[CustomerID]
VAR CurrentYear = 'Customer Table'[Year]
RETURN
    IF(
        'Customer Table'[Bal units] < 1 &&
        'Customer Table'[CustomerID] = CurrentCustomerID,
        CALCULATE(MIN('Customer Table'[Year]), 'Customer Table'[Bal units] < 1, 'Customer Table'[CustomerID]     `= CurrentCustomerID && 'Customer Table'[Year] <= CurrentYear),
        BLANK()
    )

Answer 1

Try:

FirstYearWhenZero = 
  var thisYear = 'Customer Table'[Year]
  var result = 
  CALCULATE(
    MIN('Customer Table'[Year]),
    ALLEXCEPT('Customer Table', 'Customer Table'[CustomerID]),
    'Customer Table'[Bal units] < 1 &&
    'Customer Table'[Year] <= thisYear
  )
  RETURN result

Your CALCULATE line needs an ALL('Customer Table') filter so that it looks across the whole table. I used ALLEXCEPT(...) which is similar but only returning all the rows of the same CustomerID.