Calculated column with group value based on effective date

Hi Anonymous ,

Please refer this formula.

Column =
VAR _Effective =
    CALCULATE (
        MAX ( 'Category Bands'[Effective Date] ),
        FILTER (
            'Category Bands',
            'Category Bands'[Category] = 'Category Values'[Category]
                && 'Category Bands'[Effective Date] <= 'Category Values'[Date]
        )
    )
VAR _1st =
    CALCULATE (
        MAX ( 'Category Bands'[1st Class] ),
        FILTER (
            'Category Bands',
            'Category Bands'[Category] = 'Category Values'[Category]
                && 'Category Bands'[Effective Date] = _Effective
        )
    )
VAR _max =
    MID ( _1st, 2, 9999 ) + 0
VAR _3rd =
    CALCULATE (
        MAX ( 'Category Bands'[3rd Class] ),
        FILTER (
            'Category Bands',
            'Category Bands'[Category] = 'Category Values'[Category]
                && 'Category Bands'[Effective Date] = _Effective
        )
    )
VAR _min =
    MID ( _3rd, 2, 9999 ) + 0
RETURN
    SWITCH (
        TRUE (),
        'Category Values'[Value] > _max, "1st",
        'Category Values'[Value] < _min, "3rd",
        "2nd"
    )

Best Regards,

Jay