Calculate By Parallel Period LAMBDA Function

CALCBYPARALLELPERIOD.lambda

/*
Calculate By Parallel Period LAMBDA function was inspired by Helen Wall (https://www.linkedin.com/in/helenrmwall/)
who posted a clever April fools post showing off the "new DAX measure functionality" in Excel (https://www.linkedin.com/posts/helenrmwall_excel-dax-dataanalytics-activity-6915787496492560384-71V0?utm_source=linkedin_share&utm_medium=member_desktop_web)
which was =CALCULATE(AVERAGE(Rate,PARALLELPERIOD(Date,0,MONTH))).  Of course this is sadly not possible
in Excel formulas.  But since I'm obsessed with the LAMBDA function I took it as a challenge to create 
a CALCBYPARALLELPERIOD function that anyone can use to have the same functionality as what Helen teased
could be done in formulas.  Thanks to Helen for the idea, and also thanks to Wall Street Prep for their 
EOQUARTER function included below and used in this solution (https://www.wallstreetprep.com/knowledge/excel-lambda-function).

* Please feel use and share this collection, courtesy of Excel Robot.

Included below:
CALCBYPARALLELPERIOD - calculate by leading or trailing month, quarter, or year periods using
   a variety of calculation methods
PARALLELPERIOD - returns all the dates in parallel leading or trailing months, quarters, or years 
   relative to a single date value
EOQUARTER - Just like EOMONTH but by quarters (credit: Wall Street Prep)

*/

/*
Name: Calculate By Parallel Period (CALCBYPARALLELPERIOD)
Description: Equivalent to DAX formula =CALCULATE(AVERAGE(Value,PARALLELPERIOD(Date,0,MONTH))),
    except this supports all the same functions as Excel's SUBTOTAL function (https://support.microsoft.com/en-us/office/subtotal-function-7b027003-f060-4ade-9040-e478765b9939)
    ie: 1=AVERAGE, 4=MAX, 5=MIN, 7=STDEV, 9=SUM, etc. This supports period intervals of month,
    quarter, and year.  The result is a dynamic array that will fill down automatically the 
    same number of columns as the values array passed.
    Example:  =CALCBYPARALLELPERIOD(1, MyValueRange, MyDateRange, 0, "MONTH")
Author: ExcelRobot (@ExcelRobot)
Category: DAX
*/
CALCBYPARALLELPERIOD=LAMBDA(function_num, values, dates, number_of_intervals, interval,
    BYROW(dates,
        LAMBDA(date,LET(
            parallelperiod, PARALLELPERIOD(date,number_of_intervals,interval),
            startdate, MIN(parallelperiod),
            enddate, MAX(parallelperiod),
            vals, FILTER(values,(dates>=startdate)*(dates<=enddate)),
            calc, CHOOSE(function_num, 
                AVERAGE(vals),
                COUNT(vals),
                COUNTA(vals),
                MAX(vals),
                MIN(vals),
                PRODUCT(vals),
                STDEV.S(vals),
                STDEV.P(vals),
                SUM(vals),
                VAR.S(vals),
                VAR.P(vals)
                ),
            calc
        ))
));

/*
Name: Parallel Period (PARALLELPERIOD)
Description: Inspired by the PARALLELPERIOD function available in DAX (https://docs.microsoft.com/en-us/dax/parallelperiod-function-dax).
    Given a date, returns a dynamic array of dates across the leading or trailing month, 
    quarter, and year periods relative to that date.  
    Example:  =PARALLELPERIOD(TODAY(), -1, "QUARTER") returns an array of all the dates in last quarter.
Author: ExcelRobot (@ExcelRobot)
Category: DAX
*/
PARALLELPERIOD=LAMBDA(date, number_of_interval, interval, LET(
    startdate, SWITCH(LEFT(interval,1),
        "M", EOMONTH(date,number_of_interval-1)+1,
        "Q", EOQUARTER(date,number_of_interval-1)+1,
        "Y", DATE(YEAR(date)+number_of_interval,1,1)
        ),
    enddate, SWITCH(LEFT(interval,1),
        "M", EOMONTH(date,number_of_interval),
        "Q", EOQUARTER(date,number_of_interval),
        "Y", DATE(YEAR(date)+number_of_interval,12,31)
        ),
    period, SEQUENCE(enddate-startdate+1,1,startdate),
    period
));

/*
Name: End Of Quarter (EOQUARTER)
Description: Similar to the EOMONTH function available in Excel (https://support.microsoft.com/en-us/office/eomonth-function-7314ffa1-2bc9-4005-9d66-f49db127d628).
    Given a date, returns the serial number for the last day of the quarter that is in the indicated
    number of quarters before or after start_date.  
    Example:  =EOQUARTER(TODAY(), -1) returns last date in prior quarter from today.
Author: Wall Street Prep (https://www.wallstreetprep.com/knowledge/excel-lambda-function)
Category: Date
*/
EOQUARTER=LAMBDA(start_date, quarters,
    EOMONTH(
        EOMONTH(start_date, (ROUNDUP(MONTH(start_date) / 3, 0) * 3 - MONTH(start_date))),
        quarters * 3
    )
);

Amazing. Really really useful.