DAX VAR Function

Very difficult to troubleshoot without sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

That being said, I would change your return to test the various parts of your formula, such as:

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)

return

VAR Terminations12MTD = CALCULATE(CentreTerminations,DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-12, MONTH),'Calendar'[Month Offset]>0)
VAR Headcount12MTD = CALCULATE(AVERAGEX(ALLSELECTED('Calendar'),CentreHeadcount),DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-12, MONTH),'Calendar'[Month Offset]>0)

return
Terminations12MTD
//CALCULATE(IF(DIVIDE(Terminations12MTD,Headcount12MTD)=BLANK(),0,DIVIDE(Terminations12MTD,Headcount12MTD)),'Calendar'[Month Offset]>0)

So, see if that value is BLANK and if it is that would explain why you are getting 0% back

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

Greg_Deckler  Anonymous 

Any suggestions as to how I can improve these Measures?

My aim is to have them reduced from 5 to 1 and refrain from using ALLSELECTED if that isn't advisable, I provided a sample file in an earlier post.