What if we want to reset the results of LAMBDA back to the [initial_value] of 0 (zero) each time we encounter a new year’s sales?
We need to program the SCAN function to examine the “Month” column in the “Sales” table to detect the month of “Jan” or any other month you may be using, such as Fiscal Years that begin in “Oct” or any other month.
As our dates in the “Month” column are input as actual dates…
… we can’t just look for the string “Jan”, as “Jan-19” is the formatted version of “1/1/2019”.
We begin the SCAN formula as before:
=SCAN(0, Sales[Sales], LAMBDA(a, b,
At this point, we don’t want to blindly calculate “a + b”; we want to check the date to see if it is January or some other month.
As we are not looking at the “Month” column (column A), we have to use a function that will tell LAMBDA to “look one cell to the left”.
We can use the OFFSET function for this step.
The OFFSET function will need to start from the current location (this will be wherever “b” happens to be at the moment), then move no rows up or down (0), then move 1 column to the left (-1).
OFFSET(b, 0, -1)
The result of the OFFSET will be examined by the MONTH function to extract the month number.
MONTH(OFFSET(b, 0, -1) )
If the currently examined date is for June, the result would be “6”.
This result will be tested using an IF function to see if the result is any value other than 1 (January).
IF(MONTH(OFFSET(b, 0, -1) ) <> 1
NOTE: If you are using a Fiscal Year that begins in October, you would test against a 10.
IF(MONTH(OFFSET(b, 0, -1) ) <> 10
If the test results in a TRUE (i.e., any month other than January), we will perform the standard “a + b” formula.
IF(MONTH(OFFSET(b, 0, -1) ) <> 1, a + b
If the test results in a FALSE (i.e., this is a January date), we want to reset the accumulator back to 0 (zero). This can be accomplished by multiplying the accumulator (“a”) by 0 (zero) then adding that result to “b”.
IF(MONTH(OFFSET(b, 0, -1) ) <> 1, a + b, 0 * a + b)
The final formula would appear as follows.
=SCAN(0, Sales[Sales], LAMBDA(a, b, IF(MONTH(OFFSET(b, 0, -1) ) <> 1, a + b, 0 * a + b) ) )
You could also write the IF test so that it checks for “1” as the month (January) and performs the accumulator reset, otherwise, it runs the “a + b” formula.
=SCAN(0, Sales[Sales], LAMBDA(a, b, IF(MONTH(OFFSET(b, 0, -1) ) = 1, 0 * a + b , a + b) ) )