Calculate with and without variables difference

[Anonymous]

I have 2 calculate formulas: 

Formula 1: 

Emp Exits =

        CALCULATE(

            SUM(Emp_Exits[Employee Exits]),

            Emp_Exits[MonthNo]<=MAX(Emp_Exits[MonthNo]),

            ALL(Emp_Exits[Month Name])

            )

Formula 2:

Emp Exits with variable =

    VAR EmpExits = SUM(Emp_Exits[Employee Exits])

    RETURN

        CALCULATE(

            EmpExits,

            Emp_Exits[MonthNo]<=MAX(Emp_Exits[MonthNo]),

            ALL(Emp_Exits[Month Name])

            )

Both the formulas are same except that in Formula 2, i used variable.  See the below output screenshot.

Formula 1 is giving correct result and formula 2 is not giving the correct result. Why is this behaviour?

@amitchandak  @Greg_Deckler @tamerj1 

[tamerj1 · Accepted solution]

Hi @Anonymous 
The measure

Emp Exits with variable =
VAR EmpExits =
    SUM ( Emp_Exits[Employee Exits] )
RETURN
    CALCULATE (
        EmpExits,
        Emp_Exits[MonthNo] <= MAX ( Emp_Exits[MonthNo] ),
        ALL ( Emp_Exits[Month Name] )
    )

is exactly the same as 

Emp Exits with variable =
SUM ( Emp_Exits[Employee Exits] )

The reason is that variables are only evaluated once therefore cannot be recalculated as measures inside a CALCULATE statement. Thus only the value of the first measure is always returned.

[Ajendra]

Hey,

Please try this one!!

[Anonymous]

The MonthName column is derived from Month column using formula FORMAT([Month],"MMM").

DATESYTD is working fine with Month but not working with MonthName column which is derived from Month column:

Formula:

CALCULATE(

        (1-DIVIDE(SUM(Emp_Exits[Employee Exits]),SUM(Emp_Exits[Approved Headcount]))),

        DATESYTD(Emp_Exits[Month])

        )

[Ajendra]

Try to create a seperate date table as shown below then apply your DAX accordingly.

DateMaster =

    ADDCOLUMNS(CALENDAR(MIN(FactTable[Date]),MAX(FactTable[Date]))

    ,"Month",FORMAT([Date],"MMMM")

    ,"Sorting",FORMAT([Date],"YYYYMM")

    )