How to write dynamic years

Hi Anonymous ,

According to your description, here's my solution.

CAPITAL_2022 = 
MAXX (
    FILTER (
        ALL ( 'GP_ALLOCATION_AND_FMSUMMARY_V' ),
        'GP_ALLOCATION_AND_FMSUMMARY_V'[FUNDING TYPE] = "Capital"
            && 'GP_ALLOCATION_AND_FMSUMMARY_V'[YEAR ID] = YEAR ( TODAY () )
    ),
    'GP_ALLOCATION_AND_FMSUMMARY_V'[TOTAL ALLOCATION]
)

CAPITAL_2021 = 
MAXX (
    FILTER (
        ALL ( 'GP_ALLOCATION_AND_FMSUMMARY_V' ),
        'GP_ALLOCATION_AND_FMSUMMARY_V'[FUNDING TYPE] = "Capital"
            && 'GP_ALLOCATION_AND_FMSUMMARY_V'[YEAR ID] = YEAR ( TODAY () )-1
    ),
    'GP_ALLOCATION_AND_FMSUMMARY_V'[TOTAL ALLOCATION]
)

CAPITAL_2023 = 
MAXX (
    FILTER (
        ALL ( 'GP_ALLOCATION_AND_FMSUMMARY_V' ),
        'GP_ALLOCATION_AND_FMSUMMARY_V'[FUNDING TYPE] = "Capital"
            && 'GP_ALLOCATION_AND_FMSUMMARY_V'[YEAR ID] = YEAR ( TODAY () )+1
    ),
    'GP_ALLOCATION_AND_FMSUMMARY_V'[TOTAL ALLOCATION]
)

CAPITAL_2024 = 
MAXX (
    FILTER (
        ALL ( 'GP_ALLOCATION_AND_FMSUMMARY_V' ),
        'GP_ALLOCATION_AND_FMSUMMARY_V'[FUNDING TYPE] = "Capital"
            && 'GP_ALLOCATION_AND_FMSUMMARY_V'[YEAR ID] = YEAR ( TODAY () )+2
    ),
    'GP_ALLOCATION_AND_FMSUMMARY_V'[TOTAL ALLOCATION]
)

 Get the expected result.

I have a doubt, why not directly write [YEAR ID]="2021", [YEAR ID]="2022" etc, as when it comes to 2023, the result of the above formula is wrong, the CAPITAL_2023 should be [YEAR ID]=YEAR(TODAY()).

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.