DAX VAR Function

Hi there.

I'd like to first kindly ask you: Do not create monster formulas. It's not fun, I assure you, especially for those who will have to maintain such code. Also, please format your measures when you post them on the forum. Please respect your readers' time. Thanks.

Now I'll tell you why your monster does not work. It's because variables to which you've assigned values are STATIC. They cannot be changed. Here's your measure formatted (www.daxformatter.com😞

TEST =
VAR CentreHeadcount =
    CALCULATE (
        COUNT ( 'Centre Employees'[Employee ID] ),
        'Calendar'[Month Offset] > 0
    )
VAR CentreTerminations =
    CALCULATE (
        DISTINCTCOUNT ( 'Centre Terminations'[Employee ID] ),
        'Calendar'[Month Offset] > 0
    )
VAR Terminations12MTD =
    CALCULATE (
        CentreTerminations, -- STATIC VALUE!!!
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -12, MONTH ),
        'Calendar'[Month Offset] > 0
    )
VAR Headcount12MTD =
    CALCULATE (
        AVERAGEX ( ALLSELECTED ( 'Calendar' ), CentreHeadcount ), -- = CENTREHEADCOUNT always or BLANK
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -12, MONTH ),
        'Calendar'[Month Offset] > 0
    )
RETURN
    CALCULATE (
        IF (
            DIVIDE ( Terminations12MTD, Headcount12MTD ) = BLANK (),
            0,
            DIVIDE ( Terminations12MTD, Headcount12MTD )
        ),
        'Calendar'[Month Offset] > 0
    )

Also, there's no need to have multiple RETURNs in there. This only obscures the code.

By the way, ALLSELECTED is a very complex function. The most complex function in whole DAX. Do you fully understand what it does? Do you know what shadow context is? I'll give you a hint that will save your life: Please never use in your code something the functionality of which you don't fully understand. If you do use it, you'll be having countless bugs, many of which you'll not be even aware of.

Best

D